[Oracle] decode例子

bada130

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

最近访客更多访客>>

JevonZhang8

kqogje

honghang

tcmadmin

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Oracle

Oracle DECODE

     -- 每月入职人数统计
     DECODE(字段, 条件1, 结果值1
                  条件2, 结果值2, 默认值); 
     -- 方法一
     SELECT DECODE(TO_CHAR(HIREDATE, 'MM'), '01', 1) AS "1月"
          , DECODE(TO_CHAR(HIREDATE, 'MM'), '02', 1) AS "2月"
          , DECODE(TO_CHAR(HIREDATE, 'MM'), '03', 1) AS "3月"
          , DECODE(TO_CHAR(HIREDATE, 'MM'), '04', 1) AS "4月"
          , DECODE(TO_CHAR(HIREDATE, 'MM'), '05', 1) AS "5月"
          , DECODE(TO_CHAR(HIREDATE, 'MM'), '06', 1) AS "6月"
          , DECODE(TO_CHAR(HIREDATE, 'MM'), '07', 1) AS "7月"
          , DECODE(TO_CHAR(HIREDATE, 'MM'), '08', 1) AS "8月"
          , DECODE(TO_CHAR(HIREDATE, 'MM'), '09', 1) AS "9月"
          , DECODE(TO_CHAR(HIREDATE, 'MM'), '10', 1) AS "10月"
          , DECODE(TO_CHAR(HIREDATE, 'MM'), '11', 1) AS "11月"
          , DECODE(TO_CHAR(HIREDATE, 'MM'), '12', 1) AS "12月"
       FROM SCOTT.EMP;

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

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

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