SQL 查询以获取在日历年加入的员工数量，每月细分
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1668287/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query to fetch number of employees joined over a calender year, broken down per month
提问by Sathyajith Bhat
I'm trying to find the number of employees joined over a calender year, broken down on a monthly basis. So if 15 employees had joined in January, 30 in February and so on, the output I'd like would be
我试图找出一个日历年加入的员工人数，按月细分。因此，如果 1 月有 15 名员工加入，2 月有 30 名员工，依此类推，我想要的输出将是
Month | Employees
------|-----------
Jan | 15
Feb | 30
I've come up with a query to fetch it for a particular month
我想出了一个查询来获取特定月份的信息
SELECT * FROM (
SELECT COUNT(EMP_NO), EMP_JN_DT
FROM EMP_REG WHERE
EMP_JN_DT between '01-NOV-09' AND '30-NOV-09'
GROUP BY EMP_JN_DT )
ORDER BY 2
How do I extend this for the full calender year?
我如何将其延长到整个日历年？
回答by David Aldridge
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;
If you do not have anyone join in a particular month then you'd get no row returned. To over come this you'd have to outerjoin the above to a list of months in the required year.
如果您在某个特定月份没有任何人加入，那么您将不会返回任何行。要解决这个问题，您必须将上述内容与所需年份中的月份列表相结合。
回答by James Goodwin
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;
回答by kubal5003
http://www.techonthenet.com/oracle/functions/extract.php
http://www.techonthenet.com/oracle/functions/extract.php
There is a function that returns month. What you need to do is just put it in group by
有一个返回月份的函数。你需要做的就是把它放在 group by
回答by nitesh
The number of employees in January can be selected in the following way:
可以通过以下方式选择一月份的员工人数：
SELECT EXTRACT(MONTH FROM HIREDATE) AS MONTH1, COUNT(*)
FROM employee
WHERE EXTRACT(MONTH FROM HIREDATE)=1
GROUP BY EXTRACT(MONTH FROM HIREDATE)
