Oracle多条件统计结果按列展示及ORA-00936报错求助

错误原因分析 #

1. 直接触发ORA-00936的原因：你的SQL中最后一个COUNT(CASE...)语句末尾多了一个逗号，Oracle不允许SELECT列表的最后一个元素后存在尾随逗号，这会被判定为"缺失表达式"。
2. 潜在语法冲突：SELECT中同时包含非聚合列Time和聚合函数COUNT，但未添加GROUP BY子句，这会触发ORA-00937: 不是单组分组函数错误——聚合函数用于多行统计，而Time是单行时间值，二者无法直接混用。

修正后的SQL实现 #

根据你的需求（多条件统计结果按列展示），提供两种场景的正确SQL：

场景1：仅统计各条件的总数量（一行输出所有Step结果） #

SELECT
    COUNT(CASE WHEN value = '48.8' THEN 1 END) AS Step1,
    COUNT(CASE WHEN value = '48.75' THEN 1 END) AS Step2,
    COUNT(CASE WHEN value = '48.7' THEN 1 END) AS Step3,
    COUNT(CASE WHEN value = '48.68' THEN 1 END) AS Step4,
    COUNT(CASE WHEN value = '48.6' THEN 1 END) AS Step5,
    COUNT(CASE WHEN value = '48.58' THEN 1 END) AS Step6,
    COUNT(CASE WHEN value = '48.55' THEN 1 END) AS Step7,
    COUNT(CASE WHEN value = '48.52' THEN 1 END) AS Step8
FROM MEA
WHERE ID = 5570 
  AND systime BETWEEN caltosys(&t1) AND caltosys(&t2) 
  AND ROWNUM <= 100;

• 把THEN value改为THEN 1更高效，因为COUNT仅统计非NULL值，常量1比字段值更轻量化，效果完全一致。

场景2：按时间分组统计（每行对应一个时间点的各Step数量） #

如果需要保留时间维度并按时间分组统计，需添加GROUP BY子句：

SELECT
    TO_CHAR(systocal(systime), 'yyyy-mm-dd hh24:mi:ss.ff3') AS Time,
    COUNT(CASE WHEN value = '48.8' THEN 1 END) AS Step1,
    COUNT(CASE WHEN value = '48.75' THEN 1 END) AS Step2,
    COUNT(CASE WHEN value = '48.7' THEN 1 END) AS Step3,
    COUNT(CASE WHEN value = '48.68' THEN 1 END) AS Step4,
    COUNT(CASE WHEN value = '48.6' THEN 1 END) AS Step5,
    COUNT(CASE WHEN value = '48.58' THEN 1 END) AS Step6,
    COUNT(CASE WHEN value = '48.55' THEN 1 END) AS Step7,
    COUNT(CASE WHEN value = '48.52' THEN 1 END) AS Step8
FROM MEA
WHERE ID = 5570 
  AND systime BETWEEN caltosys(&t1) AND caltosys(&t2) 
  AND ROWNUM <= 100
GROUP BY TO_CHAR(systocal(systime), 'yyyy-mm-dd hh24:mi:ss.ff3');

内容的提问来源于stack exchange，提问作者hawkiq