使用CASE WHEN語句他統(tǒng)計(jì)各個(gè)年齡段人數(shù)

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

執(zhí)行結(jié)果為

1.CASE WHEN

CASE WHEN - 結(jié)果.png

CASE WHEN - 時(shí)間.png

2.UNION ALL

UNION ALL - 結(jié)果.png

UNION ALL - 時(shí)間.png