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