[Oracle] case when例子

最新推荐文章于 2024-04-18 16:05:18 发布

iteye_9077

最新推荐文章于 2024-04-18 16:05:18 发布

阅读量259

点赞数

分类专栏： Oracle 文章标签： case when

本文链接：https://blog.csdn.net/iteye_9077/article/details/82612181

版权

Oracle 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

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