我有一个 Oracle SQL 数据库问题,我面临的问题是我正在尝试执行两个聚合操作,但我正在执行两个查询来执行它们。我正在扫描同一张桌子 (TableOne) 两次。我有一个需要发生的连接才能进行聚合。有没有办法利用 case 语句一次性完成整个查询?
select PAR, SUM(PLANQUANT) AS VALONE
from
(select ODRP.PAR, ODRP.PLANQUANT from TableOne ODRP
inner join TableTwo LOC
on ODRP.LOCID = LOC.LOCID and LOC.LOCNAME like '%_USD'
where ODRP.TYPEID IN (5,6))
group by PAR;
select PAR, SUM(PLANQUANT) AS VALTWO
from
(select ODRP.PAR, ODRP.PLANQUANT from TableOne ODRP
inner join TableTwo LOC
on ODRP.LOCID = LOC.LOCID and LOC.LOCNAME like 'BAT_USD'
where ODRP.TYPEID IN (1))
group by PAR
我相信这样的查询会以
select PAR, SUM(PLANQUANT) AS VALONE, SUM(PLANQUANT) AS VALTWO
from
(select ODRP.PAR, ODRP.PLANQUANT from TableOne ODRP
inner join TableTwo LOC
on ODRP.LOCID = LOC.LOCID and LOC.LOCNAME like '%USD'
where ODRP.TYPEID IN (1, 5, 6))
然后我知道我至少需要在 group by 和 join 中使用 case 语句?
任何帮助将不胜感激
您应该使用条件聚合。在这种情况下,我认为查询将是:
SELECT ODRP.PAR,
SUM(CASE WHEN ODRP.TYPEID IN (1) AND LOC.LOCNAME LIKE 'BAT_USD'
THEN ODRP.PLANQUANT
ELSE 0
END),
SUM(CASE WHEN ODRP.TYPEID IN (5, 6) AND LOC.LOCNAME LIKE '%USD'
THEN ODRP.PLANQUANT
ELSE 0
END)
FROM TableOne ODRP JOIN
TableTwo LOC
ON ODRP.LOCID = LOC.LOCID
WHERE ODRP.TYPEID IN (1, 5, 6) AND LOC.LOCNAME LIKE '%USD'
GROUP BY ODRP.PAR
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句