[Oracle] case when例子

bada130

浏览: 39324 次
性别:
来自: 北京

最近访客更多访客>>

JevonZhang8

kqogje

honghang

tcmadmin

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Oracle

case when

     -- 每月入职人数统计
     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;