17,377
社区成员
发帖
与我相关
我的任务
分享
SQL> WITH personal AS (
2 SELECT 'e01' emp_id,'d01'dept_id,TO_DATE('20110101','yyyymmdd') condate FROM DUAL UNION ALL
3 SELECT 'e02' emp_id,'d01'dept_id,TO_DATE('20110201','yyyymmdd') condate FROM DUAL UNION ALL
4 SELECT 'e03' emp_id,'d01'dept_id,TO_DATE('20110301','yyyymmdd') condate FROM DUAL UNION ALL
5 SELECT 'e04' emp_id,'d01'dept_id,TO_DATE('20110401','yyyymmdd') condate FROM DUAL UNION ALL
6 SELECT 'e05' emp_id,'d01'dept_id,TO_DATE('20110501','yyyymmdd') condate FROM DUAL UNION ALL
7 SELECT 'e15' emp_id,'d01'dept_id,TO_DATE('20110501','yyyymmdd') condate FROM DUAL UNION ALL
8 SELECT 'e06' emp_id,'d02'dept_id,TO_DATE('20110101','yyyymmdd') condate FROM DUAL UNION ALL
9 SELECT 'e07' emp_id,'d02'dept_id,TO_DATE('20110201','yyyymmdd') condate FROM DUAL UNION ALL
10 SELECT 'e08' emp_id,'d02'dept_id,TO_DATE('20110301','yyyymmdd') condate FROM DUAL UNION ALL
11 SELECT 'e09' emp_id,'d02'dept_id,TO_DATE('20110401','yyyymmdd') condate FROM DUAL UNION ALL
12 SELECT 'e19' emp_id,'d02'dept_id,TO_DATE('20110401','yyyymmdd') condate FROM DUAL UNION ALL
13 SELECT 'e10' emp_id,'d02'dept_id,TO_DATE('20110401','yyyymmdd') condate FROM DUAL
14 ),
15 t1 AS (
16 SELECT t.dept_id,
17 TO_CHAR(t.condate, 'yyyymm') condate,
18 COUNT(*) cnt
19 FROM personal t
20 GROUP BY t.dept_id,
21 TO_CHAR(t.condate, 'yyyymm')
22 ),
23 year_months AS (
24 SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20110101', 'yyyymmdd'), LEVEL - 1),'yyyymm') condate
25 FROM DUAL
26 CONNECT BY LEVEL < 16
27 )
28 SELECT DECODE(n.rn, 1, n.dept_id) dept_id,
29 n.dept_cnt,
30 n.condate,
31 n.cnt
32 FROM (SELECT m1.*,
33 m2.dept_cnt,
34 row_number() OVER(PARTITION BY m1.dept_id ORDER BY m1.dept_id, m1.condate) rn
35 FROM (SELECT t1.dept_id,
36 ym.condate,
37 NVL(MAX(t1.cnt), 0) cnt
38 FROM year_months ym
39 LEFT OUTER JOIN t1 PARTITION BY(t1.dept_id) ON t1.condate =
40 ym.condate
41 GROUP BY t1.dept_id,
42 ym.condate) m1
43 LEFT OUTER JOIN (SELECT dept_id,
44 COUNT(*) dept_cnt
45 FROM personal
46 GROUP BY dept_id) m2 ON m1.dept_id = m2.dept_id) n
47 ;
DEPT_ID DEPT_CNT CONDATE CNT
------- ---------- ------- ----------
d01 6 201101 1
6 201102 1
6 201103 1
6 201104 1
6 201105 2
6 201106 0
6 201107 0
6 201108 0
6 201109 0
6 201110 0
6 201111 0
6 201112 0
6 201201 0
6 201202 0
6 201203 0
d02 6 201101 1
6 201102 1
6 201103 1
6 201104 3
6 201105 0
DEPT_ID DEPT_CNT CONDATE CNT
------- ---------- ------- ----------
6 201106 0
6 201107 0
6 201108 0
6 201109 0
6 201110 0
6 201111 0
6 201112 0
6 201201 0
6 201202 0
6 201203 0
30 rows selected
<table width="60%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="27%" height="25" align="center">部门ID</td>
<td width="35%" height="25" align="center">部门总人数</td>
<td width="18%" height="25" align="center">时间 </td>
<td width="20%" height="25" align="center">该时间合同到期人数</td>
</tr>
<tr>
<td width="27%" height="25" align="center">1</td>
<td width="35%" height="25" align="center">5</td>
<td width="18%" height="25" align="center">201101 </td>
<td width="20%" height="25" align="center">1</td>
</tr>
<tr>
<td width="27%" height="25" align="center"> </td>
<td width="35%" height="25" align="center"> </td>
<td width="18%" height="25" align="center">201102</td>
<td width="20%" height="25" align="center">0</td>
</tr>
<tr>
<td width="27%" height="25" align="center"> </td>
<td width="35%" height="25" align="center"> </td>
<td width="18%" height="25" align="center">201103</td>
<td width="20%" height="25" align="center">2</td>
</tr>
<tr>
<td width="27%" height="25" align="center"> </td>
<td width="35%" height="25" align="center"> </td>
<td width="18%" height="25" align="center">``` </td>
<td width="20%" height="25" align="center">1</td>
</tr>
<tr>
<td width="27%" height="25" align="center"> </td>
<td width="35%" height="25" align="center"> </td>
<td width="18%" height="25" align="center">201204</td>
<td width="20%" height="25" align="center">0</td>
</tr>
<tr>
<td width="27%" height="25" align="center">2</td>
<td width="35%" height="25" align="center">10</td>
<td width="18%" height="25" align="center">201101 </td>
<td width="20%" height="25" align="center">1</td>
</tr>
<tr>
<td width="27%" height="25" align="center"> </td>
<td width="35%" height="25" align="center"> </td>
<td width="18%" height="25" align="center">201102</td>
<td width="20%" height="25" align="center">0</td>
</tr>
<tr>
<td width="27%" height="25" align="center"> </td>
<td width="35%" height="25" align="center"> </td>
<td width="18%" height="25" align="center">201103</td>
<td width="20%" height="25" align="center">2</td>
</tr>
<tr>
<td width="27%" height="25" align="center"> </td>
<td width="35%" height="25" align="center"> </td>
<td width="18%" height="25" align="center">``` </td>
<td width="20%" height="25" align="center">1</td>
</tr>
<tr>
<td width="27%" height="25" align="center"> </td>
<td width="35%" height="25" align="center"> </td>
<td width="18%" height="25" align="center">201204</td>
<td width="20%" height="25" align="center">0</td>
</tr>
<tr>
<td width="27%" height="25" align="center">3</td>
<td width="35%" height="25" align="center">15</td>
<td width="18%" height="25" align="center">201101 </td>
<td width="20%" height="25" align="center">1</td>
</tr>
<tr>
<td width="27%" height="25" align="center"> </td>
<td width="35%" height="25" align="center"> </td>
<td width="18%" height="25" align="center">201102</td>
<td width="20%" height="25" align="center">0</td>
</tr>
<tr>
<td width="27%" height="25" align="center"> </td>
<td width="35%" height="25" align="center"> </td>
<td width="18%" height="25" align="center">201103</td>
<td width="20%" height="25" align="center">2</td>
</tr>
<tr>
<td width="27%" height="25" align="center"> </td>
<td width="35%" height="25" align="center"> </td>
<td width="18%" height="25" align="center">``` </td>
<td width="20%" height="25" align="center">1</td>
</tr>
<tr>
<td width="27%" height="25" align="center"> </td>
<td width="35%" height="25" align="center"> </td>
<td width="18%" height="25" align="center">201204</td>
<td width="20%" height="25" align="center">0</td>
</tr>
</table>
select dept_id,
count(1),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),0) and add_month(last_day(to_date('20110101','yyyymmdd')),0) then 1 else 0 end),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),1) and add_month(last_day(to_date('20110101','yyyymmdd')),1) then 1 else 0 end),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),2) and add_month(last_day(to_date('20110101','yyyymmdd')),2) then 1 else 0 end),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),3) and add_month(last_day(to_date('20110101','yyyymmdd')),3) then 1 else 0 end),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),4) and add_month(last_day(to_date('20110101','yyyymmdd')),4) then 1 else 0 end),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),5) and add_month(last_day(to_date('20110101','yyyymmdd')),5) then 1 else 0 end),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),6) and add_month(last_day(to_date('20110101','yyyymmdd')),6) then 1 else 0 end),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),7) and add_month(last_day(to_date('20110101','yyyymmdd')),7) then 1 else 0 end),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),8) and add_month(last_day(to_date('20110101','yyyymmdd')),8) then 1 else 0 end),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),9) and add_month(last_day(to_date('20110101','yyyymmdd')),9) then 1 else 0 end),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),10) and add_month(last_day(to_date('20110101','yyyymmdd')),10) then 1 else 0 end),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),11) and add_month(last_day(to_date('20110101','yyyymmdd')),11) then 1 else 0 end),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),12) and add_month(last_day(to_date('20110101','yyyymmdd')),12) then 1 else 0 end),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),13) and add_month(last_day(to_date('20110101','yyyymmdd')),13) then 1 else 0 end),
sum(case when a.condate between add_month(to_date('20110101','yyyymmdd'),14) and add_month(last_day(to_date('20110101','yyyymmdd')),14) then 1 else 0 end)
from personal a
group by dept_id
;