Count(*)在SQL Developer(ORACLE)中返回(行数-1)

斯蒂芬·卡(Stefan Ca)

我试图在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;
戈登·利诺夫(Gordon Linoff)

我认为您需要条件聚合:

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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章