我想找出當年（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。
uj5u.com熱心網友回復：
在 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>
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/333121.html
上一篇：Oracle將一個程序重寫為泛型