oracle case when 求和,Oracle之--CASE WHEN语句

使用CASE WHEN语句他统计各个年龄段人数

SELECT

SUM(CASE WHEN (TO_CHAR( SYSDATE, 'YYYY' ) - SUBSTR( t.INPUT_IDCARD, 7, 4 ) ) BETWEEN 20 AND 30 THEN 1 ELSE 0 END) AS "20-30岁",

SUM(CASE WHEN (TO_CHAR( SYSDATE, 'YYYY' ) - SUBSTR( t.INPUT_IDCARD, 7, 4 ) ) BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS "31-40岁",

SUM(CASE WHEN (TO_CHAR( SYSDATE, 'YYYY' ) - SUBSTR( t.INPUT_IDCARD, 7, 4 ) ) BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS "41-50岁",

SUM(CASE WHEN (TO_CHAR( SYSDATE, 'YYYY' ) - SUBSTR( t.INPUT_IDCARD, 7, 4 ) ) BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS "51-60岁"

FROM t

遇到需要整合的sql是使用CASE WHEN还是UNION呢?

SELECT COUNT(*) FROM t WHERE (TO_CHAR( SYSDATE, 'YYYY' ) - SUBSTR( INPUT_IDCARD, 7, 4 ) ) BETWEEN 20 AND 30

UNION ALL

SELECT COUNT(*) FROM t WHERE (TO_CHAR( SYSDATE, 'YYYY' ) - SUBSTR( INPUT_IDCARD, 7, 4 ) ) BETWEEN 31 AND 40

UNION ALL

SELECT COUNT(*) FROM t WHERE (TO_CHAR( SYSDATE, 'YYYY' ) - SUBSTR( INPUT_IDCARD, 7, 4 ) ) BETWEEN 41 AND 50

UNION ALL

SELECT COUNT(*) FROM t WHERE (TO_CHAR( SYSDATE, 'YYYY' ) - SUBSTR( INPUT_IDCARD, 7, 4 ) ) BETWEEN 51 AND 60

执行结果为

1.CASE WHEN

CASE WHEN - 结果.png

CASE WHEN - 时间.png

2.UNION ALL

UNION ALL - 结果.png

UNION ALL - 时间.png