Question

在我的一项评估中,我需要计算燃料消耗量,同时考虑到完全的再燃料和部分再燃料。我的安乐施会基于Qallite syntax和特征。

我试图解决库福尔的这种难题,不想走 all,让所有各行走,以在方案上做得不错。

应在结果清单中删除与挪威人民解放阵线的罗,但其价值应当列入下届民族解放阵线。 Y row。至少这是我的想法。

这是DML(仅需要一栏):

CREATE TABLE FUELINGS (
ID INTEGER PRIMARY KEY,
MILEAGE INTEGER,
VOLUME DECIMAL(8, 2),
FULL CHAR(1));

下表提供了一些投入:

INSERT INTO FUELINGS VALUES(1,22995,29.48, Y );
INSERT INTO FUELINGS VALUES(2,23385,31.27, Y );
INSERT INTO FUELINGS VALUES(3,23869,16.71, N );
INSERT INTO FUELINGS VALUES(4,24065,33.18, Y );
INSERT INTO FUELINGS VALUES(5,24485,30.59, Y );
INSERT INTO FUELINGS VALUES(6,24956,33.78, Y );

Ok, 这里是问题。这是我使用的问题:

SELECT T2.MILEAGE, 
       T2.VOLUME, 
       T2.FULL, 
       T2.PREVMILEAGE, 
       T2.PREVVOLUME, 
       T2.PREVFULL, 
       100 * T2.VOLUME / ( T2.MILEAGE - T2.PREVMILEAGE ) 
FROM   (SELECT T.MILEAGE, 
               T.VOLUME, 
               T.FULL, 
               T.PREVMILEAGE, 
               (SELECT VOLUME 
                FROM   FUELINGS 
                WHERE  MILEAGE = T.PREVMILEAGE 
                       AND MILEAGE IS NOT NULL 
                       AND MILEAGE > 0 
                       AND VOLUME IS NOT NULL 
                       AND VOLUME > 0) AS PREVVOLUME, 
               (SELECT FULL 
                FROM   FUELINGS 
                WHERE  MILEAGE = T.PREVMILEAGE 
                       AND MILEAGE IS NOT NULL 
                       AND MILEAGE > 0 
                       AND VOLUME IS NOT NULL 
                       AND VOLUME > 0) AS PREVFULL 
        FROM   (SELECT F.MILEAGE, 
                       F.VOLUME, 
                       F.FULL, 
                       (SELECT Max(MILEAGE) 
                        FROM   FUELINGS PREV 
                        WHERE  PREV.MILEAGE < F.MILEAGE 
                               AND MILEAGE IS NOT NULL 
                               AND MILEAGE > 0 
                               AND VOLUME IS NOT NULL 
                               AND VOLUME > 0) AS PREVMILEAGE 
                FROM   FUELINGS F 
                WHERE  MILEAGE IS NOT NULL 
                       AND MILEAGE > 0 
                       AND VOLUME IS NOT NULL 
                       AND VOLUME > 0) AS T 
        WHERE  PREVMILEAGE IS NOT NULL) AS T2 
ORDER  BY T2.MILEAGE;

结果是:

T2.MILEAGE  T2.VOLUME   T2.FULL T2.PREVMILEAGE  T2.PREVVOLUME   T2.PREVFULL 100 * T2.VOLUME / (T2.MILEAGE - T2.PREVMILEAGE)
23385   31.27   Y   22995   29.48   Y   8.017948717948718
23869   16.71   N   23385   31.27   Y   3.4524793388429753
24065   33.18   Y   23869   16.71   N   16.928571428571427
24485   30.59   Y   24065   33.18   Y   7.283333333333333
24956   33.78   Y   24485   30.59   Y   7.171974522292993

16.92 是错误的,因为在此之前的再燃料部分是加油。我需要总结和计算两行。我知道,可能会有超过1个部分的再燃料。但是,我没有任何东西可以用来建立民族解放阵线的范围。 N Value, amount them up and add them to the following FUL= Y数值。

有些人可以把我放在正确方向。我怎么能使这一权利成为现实?

Many thanks in advance.

<<>strong>EDIT/SOLUTION:

SELECT T.MILEAGE,
       T.PREVMILEAGE,
       SUM(VOLUME),
       100 * SUM(VOLUME) / ( T.MILEAGE - T.PREVMILEAGE )
FROM   (SELECT F1.MILEAGE      AS MILEAGE,
               MAX(F2.MILEAGE) AS PREVMILEAGE
        FROM   FUELINGS F1
               LEFT OUTER JOIN FUELINGS F2
                 ON F1.MILEAGE > F2.MILEAGE
                    AND F2.FULL =  Y 
                    AND F2.MILEAGE IS NOT NULL
                    AND F2.MILEAGE > 0
                    AND F2.VOLUME IS NOT NULL
                    AND F2.VOLUME > 0.0
        WHERE  F1.FULL =  Y 
               AND F1.MILEAGE IS NOT NULL
               AND F1.MILEAGE > 0
               AND F1.VOLUME IS NOT NULL
               AND F1.VOLUME > 0.0
        GROUP  BY F1.MILEAGE) AS T,
       (SELECT VOLUME
        FROM   FUELINGS F3
        WHERE  F3.MILEAGE > T.PREVMILEAGE
               AND F3.MILEAGE <= T.MILEAGE)
GROUP  BY T.MILEAGE

Answer 1

如果你有一个称为“燃料未来”的表格,你最初的询问会奏效,该表仅载有关于全部燃料的全部信息。这一点可以从燃料收集中总结。这一过程将更加简单,具有窗户功能,但你没有窗户。

So, here is an idea on how to do this. First, get the previous full id:

select f1.id, max(f2.full) as prevFullId
from fuelings f1 left outer join
     fuelings f2
     on f1.id > f2.id and
        f2.full = 0
group by f1.id

现在,你们都有一批燃料,它们都属于一起。接下来的问询会回头看燃料,然后将这些燃料集中起来。

select fills.id, max(mileage) as mileage, sum(volume) as volume, count(*) as numFills
from fuelings f join
     (select f1.id, max(f2.full) as prevFullId
      from fuelings f1 left outer join
           fuelings f2
           on f1.id > f2.id and
              f2.full = 1
      group by f1.id
     ) fills
     on f.id > fills.prevFullId and f.id <= fills.id
  group by fills.id

既然是你的燃料未来,你就应该先问。

友情链接