我试图在sql developer(emp table)中获得此结果:
TOTAL 1980 1981 1982 1983
-------- --------- --------- --------- ---------
14 1 10 2 1
但我得到:
TOTAL 1980 1981 1982 1983
-------- --------- --------- --------- ---------
13 1 10 2 1
指望整个桌子,我得到了14:
SELECT COUNT(*) FROM EMP;
为什么在这种情况下计数返回13而不是14?
SELECT COUNT(*) TOTAL,
SUM(DECODE(EXTRACT(YEAR FROM HIREDATE),1980,COUNT(*))) "1980",
SUM(DECODE(EXTRACT(YEAR FROM HIREDATE),1981,COUNT(*))) "1981",
SUM(DECODE(EXTRACT(YEAR FROM HIREDATE),1982,COUNT(*))) "1982",
SUM(DECODE(EXTRACT(YEAR FROM HIREDATE),1983,COUNT(*))) "1983"
FROM EMP GROUP BY HIREDATE;
我认为您需要条件聚合:
SELECT COUNT(*) TOTAL,
SUM(CASE WHEN EXTRACT(YEAR FROM HIREDATE) = 1980 THEN 1 ELSE 0 END) as "1980",
SUM(CASE WHEN EXTRACT(YEAR FROM HIREDATE) = 1981 THEN 1 ELSE 0 END) as "198`",
SUM(CASE WHEN EXTRACT(YEAR FROM HIREDATE) = 1982 THEN 1 ELSE 0 END) as "1982",
SUM(CASE WHEN EXTRACT(YEAR FROM HIREDATE) = 1983 THEN 1 ELSE 0 END) as "1983"
FROM EMP;
笔记:
GROUP BY HIREDATE
。您似乎只想要一行输出。DECODE()
是特定于Oracle的。条件表达式的ANSI标准为CASE
。COUNT(*)
在内部SUM()
)。这是导致您出现问题的原因。此版本应解决此问题。本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句