一张报表的SQL语句，请帮忙！！

2011-06-28 08:54:04 254 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

a120255857 墨衍会员 · AI 创作全网分发关注 + 加入社区

如何在uReport2的SQL语句中添加查询参数并且访问报表页面        相信看过第二章link《uReport2报表工具的基础使用及注意事项》的读者们应该对uReport2的基础操作会有所了解的，本章就开始讲述一下，如何在uReport2的SQL语句中添加查询参数并且访问报表页面。（一）添加查询参数        因为uReport2的编写入口支持表达式解析，不难想到可以用${}的方式进行编写。当然，此处需要借助param( 阅读详情

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

请各位帮帮忙，谢谢！如有疑问请提出来。这个就是

...全文

利用SQL Server 进行报表统计的关键SQL语句与函数利用以上的SQL语句和函数，可以轻松实现对时间进行分组的报表统计功能。开发人员可以根据具体需求，灵活运用这些语句和函数，为企业提供准确、直观的时间分组报表数据。阅读详情

a120255857

基础和管理 17,379 成员 95,111 内容

+ 关注 + 加入社区

切换为时间正序

不要悲剧人生 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

打赏
举报

给些数据样式，然后给部分结果样式................

一张报表的SQL语句，请帮忙！！

相关推荐