SQL-WITH ... AS的替代方法 [英] Sql - alternative to WITH ... AS
本文介绍了SQL-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屋!
查看全文