SQL查询以获取日历年内加入的员工数,按月细分

SELECT   Trunc(EMP_JN_DT,'MM') Emp_Jn_Mth,
         Count(*)
FROM     EMP_REG
WHERE    EMP_JN_DT between date '2009-01-01' AND date '2009-12-31'
GROUP BY Trunc(EMP_JN_DT,'MM')
ORDER BY 1;

如果您在某个特定月份没有任何人加入,则不会返回任何行。为了克服这一点,你必须在所需年份的月份列表中加入上述内容。

SELECT to_date(EMP_JN_DT,'MON') "Month", EMP_NO "Employees"
FROM EMP_REG
WHERE EMP_JN_DT between date '2009-01-01' AND date '2009-12-31'
GROUP by "Month"
ORDER BY 1;

http://www.techonthenet.com/oracle/functions/extract.php

有一个函数返回月份。你需要做的就是把它分组

1月份的员工人数可通过以下方式选择:

SELECT EXTRACT(MONTH FROM HIREDATE) AS MONTH1, COUNT(*)
FROM employee
WHERE EXTRACT(MONTH FROM HIREDATE)=1
GROUP BY EXTRACT(MONTH FROM HIREDATE)