在Oracle中查找基于雇用日期的员工数

Question

我想知道在2020年1月到12月之间加入的员工人数。假设任何员工在某个特定的月份没有加入，计数应该显示为0。

Empno Ename  Hiredate     deptno
101   ram    11-Jan-20    10
102   kumar  07-Mar-20    10
103   Raja   06-Oct-20    20
104   Sasi   16-Dec-20    20
105   manoj  19-Dec-20    10
Excepted Output
Count  Hiredate_Month
1      Jan
0      Feb
1      Mar
0      Apr
0      May
0      Jun
0      Jul
0      Aug
0      Sep
1      Oct
0      Nov
2      Dec

我尝试了以下方法。

create table emp_details(empno number,ename varchar2(22),hiredate date,deptno number);
insert into emp_details values(101,'ram','11-jan-2020',10);
insert into emp_details values(102,'kumar','07-mar-2020',10);
insert into emp_details values(103,'raja','06-oct-2020',20);
insert into emp_details values(104,'sasi','16-dec-2020',20);
insert into emp_details values(105,'manoj','19-dec-2020',10);
select count(*),to_char(hiredate,'mon') from emp_details group by
to_char(hiredate,'mon')

上面的查询没有显示雇员未加入的月份的计数0。

Answer 1

在Scott的示例模式中，有EMP表:

SQL> select ename, hiredate
2  from emp
3  order by to_char(hiredate, 'mm');
ENAME      HIREDATE
---------- --------
ADAMS      12.01.83   --> Adams and Miller
MILLER     23.01.82   --> were hired in January
ALLEN      20.02.81
WARD       22.02.81
--> nobody was hired in March
JONES      02.04.81   --> Jones was hired in April
BLAKE      01.05.81
CLARK      09.06.81
TURNER     08.09.81
MARTIN     28.09.81
KING       17.11.81
SCOTT      09.12.82
SMITH      17.12.80
JAMES      03.12.81
FORD       03.12.81
14 rows selected.

为了得到你想要的结果，你需要一个日历——单独的表，它包含一年中所有的月份，因为雇员表不包含所有的月份(参见上面的-没有人在三月份被雇用)。

然后将日历与原始表外部连接，计算员工数量和-就是这样:

SQL> with calendar as
2    (select lpad(level, 2, '0') mon
3     from dual
4     connect by level <= 12
5    )
6  select to_char(to_date(c.mon, 'mm'), 'Mon', 'nls_date_language = english') hiredate_month,
7    count(e.empno) cnt
8  from calendar c left join emp e on to_char(e.hiredate, 'mm') = c.mon
9  group by c.mon
10  order by c.mon;
HIREDATE_MON        CNT
------------ ----------
Jan                   2
Feb                   2
Mar                   0
Apr                   1
May                   1
Jun                   1
Jul                   0
Aug                   0
Sep                   2
Oct                   0
Nov                   1
Dec                   4
12 rows selected.
SQL>