SQL-WITH'AS的替代

Question

此示例是否有 有效的 替代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语句。

Answer 1

只需使用条件聚合：

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;