[Oracle] case when例子

lwkkliafhc

-- 每月入职人数统计
CASE WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
ELSE 结果N END AS ALIAS
-- 方法一  
SELECT CASE WHEN TO_CHAR(HIREDATE, 'MM') = '01' THEN 1 END AS "1月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '02' THEN 1 END AS "2月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '03' THEN 1 END AS "3月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '04' THEN 1 END AS "4月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '05' THEN 1 END AS "5月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '06' THEN 1 END AS "6月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '07' THEN 1 END AS "7月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '08' THEN 1 END AS "8月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '09' THEN 1 END AS "9月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '10' THEN 1 END AS "10月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '11' THEN 1 END AS "11月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '12' THEN 1 END AS "12月"
FROM SCOTT.EMP;

     SELECT COUNT(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '01' THEN 1 END) AS "1月"
, COUNT(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '02' THEN 1 END) AS "2月"
, COUNT(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '03' THEN 1 END) AS "3月"
, COUNT(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '04' THEN 1 END) AS "4月"
, COUNT(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '05' THEN 1 END) AS "5月"
, COUNT(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '06' THEN 1 END) AS "6月"
, COUNT(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '07' THEN 1 END) AS "7月"
, COUNT(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '08' THEN 1 END) AS "8月"
, COUNT(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '09' THEN 1 END) AS "9月"
, COUNT(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '10' THEN 1 END) AS "10月"
, COUNT(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '11' THEN 1 END) AS "11月"
, COUNT(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '12' THEN 1 END) AS "12月"
FROM SCOTT.EMP;         

     -- 方法二  
SELECT CASE WHEN TO_CHAR(HIREDATE, 'MM') = '01' THEN 1 ELSE 0 END AS "1月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '02' THEN 1 ELSE 0 END AS "2月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '03' THEN 1 ELSE 0 END AS "3月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '04' THEN 1 ELSE 0 END AS "4月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '05' THEN 1 ELSE 0 END AS "5月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '06' THEN 1 ELSE 0 END AS "6月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '07' THEN 1 ELSE 0 END AS "7月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '08' THEN 1 ELSE 0 END AS "8月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '09' THEN 1 ELSE 0 END AS "9月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '10' THEN 1 ELSE 0 END AS "10月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '11' THEN 1 ELSE 0 END AS "11月"
, CASE WHEN TO_CHAR(HIREDATE, 'MM') = '12' THEN 1 ELSE 0 END AS "12月"
FROM SCOTT.EMP;

     SELECT SUM(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '01' THEN 1 ELSE 0 END) AS "1月"
, SUM(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '02' THEN 1 ELSE 0 END) AS "2月"
, SUM(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '03' THEN 1 ELSE 0 END) AS "3月"
, SUM(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '04' THEN 1 ELSE 0 END) AS "4月"
, SUM(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '05' THEN 1 ELSE 0 END) AS "5月"
, SUM(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '06' THEN 1 ELSE 0 END) AS "6月"
, SUM(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '07' THEN 1 ELSE 0 END) AS "7月"
, SUM(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '08' THEN 1 ELSE 0 END) AS "8月"
, SUM(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '09' THEN 1 ELSE 0 END) AS "9月"
, SUM(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '10' THEN 1 ELSE 0 END) AS "10月"
, SUM(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '11' THEN 1 ELSE 0 END) AS "11月"
, SUM(CASE WHEN TO_CHAR(HIREDATE, 'MM') = '12' THEN 1 ELSE 0 END) AS "12月"
FROM SCOTT.EMP;