有没有一个高效的这个例子的替代SQL？我不想使用WITH ... AS，这是主要标准
WITH TEMP_TABLE AS (
SELECT status, COUNT(1) as total FROM XYZ GROUP BY status
)
SELECT
NVL((SELECT TOTAL FROM TEMP_TABLE WHERE STATUS = 'A'),0) AS A_COUNT ,
NVL((SELECT TOTAL FROM TEMP_TABLE WHERE STATUS = 'B'),0) AS B_COUNT ,
NVL((SELECT TOTAL FROM TEMP_TABLE WHERE STATUS = 'C'),0) AS C_COUNT ,
NVL((SELECT TOTAL FROM TEMP_TABLE WHERE STATUS = 'D'),0) AS D_COUNT ,
NVL((SELECT TOTAL FROM TEMP_TABLE WHERE STATUS = 'E'),0) AS E_COUNT ,
NVL((SELECT TOTAL FROM TEMP_TABLE WHERE STATUS = 'F'),0) AS F_COUNT
FROM DUAL;
我读过类似的问题，但他们使用其他 ddl 东西，我只想要一个基本的 SQL 语句。
只需使用条件聚合：
SELECT SUM(CASE WHEN STATUS = 'A' THEN 1 ELSE 0 END) AS A_COUNT ,
SUM(CASE WHEN STATUS = 'B' THEN 1 ELSE 0 END) AS B_COUNT ,
SUM(CASE WHEN STATUS = 'C' THEN 1 ELSE 0 END) AS C_COUNT ,
SUM(CASE WHEN STATUS = 'D' THEN 1 ELSE 0 END) AS D_COUNT ,
SUM(CASE WHEN STATUS = 'E' THEN 1 ELSE 0 END) AS E_COUNT ,
SUM(CASE WHEN STATUS = 'F' THEN 1 ELSE 0 END) AS F_COUNT
FROM XYZ ;
我应该指出，将值放在单独的行上更加容易：
select status, count(*)
from xyz
group by status;
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
