SQL-WITH ... AS的替代方法 [英] Sql - alternative to WITH ... AS

问题描述

此示例是否有高效替代SQL?我不想使用WITH ... AS，这是主要标准

Is there an efficient alternative SQL to this example? I do not want to use WITH ... AS, that is the main criteria

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语句.

I read similar questions but they use other ddl stuff, I just want a basic SQL statement.

推荐答案

只需使用条件聚合:

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 ;

我应该指出，将值放在单独的行上甚至更容易:

I should point out that putting the values on separate rows is even easier:

select status, count(*)
from xyz
group by status;

这篇关于SQL-WITH ... AS的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！