查找 2 个特定行之间的差异

Question

我需要找到 2 个特定行之间的差异，但无法破解 LAG/LEAD 函数，并且不确定这些函数是否有帮助。

需要从dest=OM-OM_20的行中减去dest=OM-OM_225的行。

使用Oracle 9i。

表是使用以下命令创建的：

SELECT TRUNC(DATETIME,'HH') DATETIME,decode(OBJECT_ID,20,'OM-OM_20',225,'OM-OM_225',250,'OM-PSTN','OM-INT') DEST,

sum(BO) as CAABS,
SUM(BA+ RE) as CATT,
round(SUM((AC/22)*11,2) as CAMIN

FROM SCHEMA.TABLE
WHERE ((OBJECT_ID = 20) or  (OBJECT_ID = 225)  or (OBJECT_ID = 250) or (OBJECT_ID = 150) or (OBJECT_ID =160) or (OBJECT_ID = 161) or (OBJECT_ID = 162)  or (OBJECT_ID = 163) or (OBJECT_ID = 164) or (OBJECT_ID = 165) or(OBJECT_ID = 166)  or (OBJECT_ID = 167) ) 
and DATETIME between trunc(sysdate,'hh')-1/24 and trunc(sysdate,'hh')-1/24/3600
group by TRUNC(DATETIME,'HH'), decode(OBJECT_ID,20,'OM-OM_20',225,'OM-OM_225',250,'OM-PSTN','OM-INT')
order by 1 desc

OUTPUT

DATETIME                      DEST    CAABS           CATT           CAMINS

9/7/2009 1:00:00 PM OM-INT    10417           64670          87971.67
9/7/2009 1:00:00 PM OM-PSTN   7372            95388          13309.17
9/7/2009 1:00:00 PM OM-OM_20   6767             231884           184952.5
9/7/2009 1:00:00 PM OM-OM_225     33104           101003             68570.83

Answer 1

SELECT  SUM(DECODE(OBJECT_ID, 20, BO, -BO))
FROM    mytable
WHERE   OBJECT_ID IN (20, 225)
        AND DATETIME BETWEEN TRUNC(SYSDATE, 'HH') - 1 / 24 AND TRUNC(SYSDATE, 'hh') - 1 / 24 / 3600
GROUP BY
        TRUNC(DATETIME, 'HH')

Answer 2

如果想在同一个select中执行此操作，可以使用join：（

select T1.some_field - coalesce(T2.some_field, 0),
...
from TABLE T1
left join TABLE T2 on T2.dest = 'OM-OM_225' and T1.dest = 'OM-OM_20'

查询的具体形式可以根据DB而不同）