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