一张报表的SQL语句,请帮忙!!

a120255857 2011-06-28 08:54:04
personal 表
emp_id --员工ID
dept_id --部门ID
condate -- 合同到期时间
求一SQL 按部门分组 统计各部门总人数 ,在按月份分组 统计在每个月合同到期人数 ,如果这个月到期人数为0
则显示0, 报表会传入一个日期的参数 ,当显示的时侯 ,显示这个日期参数后14个月每个月到期人数的
分布情况

查询出来的结果如下

部门ID 该部门合同到期总人数 201101合同到期人数 201102 合同到期人数 ··循环14个月··201203合同到期人数
5 20 2 0 6
6 7 1 0 2


请各位帮帮忙 ,谢谢! 如有疑问请提出来 。这个就是
...全文
198 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
不要悲剧人生 2011-06-30
  • 打赏
  • 举报
回复
这么难
hudingchen 2011-06-30
  • 打赏
  • 举报
回复
表year_months中的'20110101'是你传递的日期
hudingchen 2011-06-30
  • 打赏
  • 举报
回复
需求有点复杂,试着写一个

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

kingwinerscxp 2011-06-29
  • 打赏
  • 举报
回复
20110101是你要传入的日期参数,personal 表的condate字段最好是DATE型,如果不是用To_Date转一下
kingwinerscxp 2011-06-29
  • 打赏
  • 举报
回复
select a.dept_id,COUNT(*)/14 AS tot,
sum(CASE WHEN b.mon=1 AND to_char(a.condate,'yyyymm')=to_char(b.dt_list,'yyyymm') THEN 1 ELSE 0 END) AS Y01,
sum(CASE WHEN b.mon=2 AND to_char(a.condate,'yyyymm')=to_char(b.dt_list,'yyyymm') THEN 1 ELSE 0 END) AS Y02,
sum(CASE WHEN b.mon=3 AND to_char(a.condate,'yyyymm')=to_char(b.dt_list,'yyyymm') THEN 1 ELSE 0 END) AS Y03,
sum(CASE WHEN b.mon=4 AND to_char(a.condate,'yyyymm')=to_char(b.dt_list,'yyyymm') THEN 1 ELSE 0 END) AS Y04,
sum(CASE WHEN b.mon=5 AND to_char(a.condate,'yyyymm')=to_char(b.dt_list,'yyyymm') THEN 1 ELSE 0 END) AS Y05,
sum(CASE WHEN b.mon=6 AND to_char(a.condate,'yyyymm')=to_char(b.dt_list,'yyyymm') THEN 1 ELSE 0 END) AS Y06,
sum(CASE WHEN b.mon=7 AND to_char(a.condate,'yyyymm')=to_char(b.dt_list,'yyyymm') THEN 1 ELSE 0 END) AS Y07,
sum(CASE WHEN b.mon=8 AND to_char(a.condate,'yyyymm')=to_char(b.dt_list,'yyyymm') THEN 1 ELSE 0 END) AS Y08,
sum(CASE WHEN b.mon=9 AND to_char(a.condate,'yyyymm')=to_char(b.dt_list,'yyyymm') THEN 1 ELSE 0 END) AS Y09,
sum(CASE WHEN b.mon=10 AND to_char(a.condate,'yyyymm')=to_char(b.dt_list,'yyyymm') THEN 1 ELSE 0 END) AS Y10,
sum(CASE WHEN b.mon=11 AND to_char(a.condate,'yyyymm')=to_char(b.dt_list,'yyyymm') THEN 1 ELSE 0 END) AS Y11,
sum(CASE WHEN b.mon=12 AND to_char(a.condate,'yyyymm')=to_char(b.dt_list,'yyyymm') THEN 1 ELSE 0 END) AS Y12,
sum(CASE WHEN b.mon=13 AND to_char(a.condate,'yyyymm')=to_char(b.dt_list,'yyyymm') THEN 1 ELSE 0 END) AS Y13,
sum(CASE WHEN b.mon=14 AND to_char(a.condate,'yyyymm')=to_char(b.dt_list,'yyyymm') THEN 1 ELSE 0 END) AS Y14
from personal a,
(select add_months(to_date('20110101','yyyymmdd'),rownum) as dt_list,ROWNUM AS mon from dual connect by rownum <=14) b
GROUP BY a.dept_id
老黎 2011-06-29
  • 打赏
  • 举报
回复
有方法,不过方法很笨,看高手出马
a120255857 2011-06-29
  • 打赏
  • 举报
回复
谢谢 2楼 ,这种方法我试过了 报表无法做成矩阵的 。
不好意思 是我把显示结果搞错了 ,应该是要用分析函数来写 。
结果显示应该如下


<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>
a120255857 2011-06-29
  • 打赏
  • 举报
回复
谢谢 2楼 ,这种方法我试过了 报表无法做成矩阵的 。
不好意思 是我把显示结果搞错了 ,应该是要用分析函数来写 。
结果显示应该如下
部门ID 部门总人数 时间 该时间合同到期人数
1 5 201101 1
201102 0
·
·
201206 1
2 10 201101 0
·
·
201206 2
3 10 201101 5
yejihui9527 2011-06-29
  • 打赏
  • 举报
回复
case decode
ckdmff101429 2011-06-29
  • 打赏
  • 举报
回复
case 表达式!
wangyiyan 2011-06-29
  • 打赏
  • 举报
回复
case是啥意思?
老黎 2011-06-29
  • 打赏
  • 举报
回复

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
;
304的的哥 2011-06-28
  • 打赏
  • 举报
回复
给些数据样式,然后给部分结果样式................

17,377

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧