求助，水晶报表之交叉表 急~~~

[zhangyyy_10]

要求

哥哥姐姐帮帮忙啊。
上面是要实现的报表，难点：行列交叉要求显示平均数，而总计要求显示平均数的和，
目前看到报表的功能是，显示平均数，总计也是显示的平均数。

结果：


这关系到我在公司的生存呀，请求各位帮助。

[WWWWA]

参考 http://bbs.csdn.net/topics/310045927 中我的回答

[zhangyyy_10]

怎么没有人回复啊，这可是我的处女帖，自己先顶，谢谢！

[WWWWA]

水晶报表没有用过 用SQL语句生成交叉表不行？

[zhangyyy_10]

引用 2 楼 WWWWA 的回复:

水晶报表没有用过 用SQL语句生成交叉表不行？

估计一个sql语句实现不了吧？又是平均又是汇总的，请大侠能不能写个相似的，让俺学习，参考下

[WWWWA]

你的数据是什么，贴表结构及记录出来看看

[wwwwb]

呵呵，ORACLE不是很熟悉，你参考那个帖子动手做一下吧

[wwwwb]

总计是怎样得出的142.2、5.33 后台数据库是什么，如果支持ROLLUP 将你上述SQL语句存为查询比如VIEW1 SELECT FRM_NAME,SUM(CASE WHEN SVC_NAME='COMMON' THEN IRDAY ELSE 0 END), SUM(CASE WHEN SVC_NAME='TAM' THEN IRDAY ELSE 0 END), SUM(CASE WHEN SVC_NAME='SOCI' THEN IRDAY ELSE 0 END),SUM(IRDAY) AS 总计 FROM VIEW1 GROUP BY FRM_NAME WITH ROLLUP

[wwwwb]

你用7楼的代码试试 你可以将FROM 后面的内容修改成你的SQL代码内容 FROM 。。。 JOIN 。。。 ON 。。。

[wwwwb]

将你的SQL语句用文本贴出来

[zhangyyy_10]

select f.frm_name,c.svc_name,ir.rpt_rcv_datetime ,r.rqst_datetime, floor(ir.rpt_rcv_datetime- r.rqst_datetime) as irday from IVG_RQST r inner join IVG_FRM f on r.IVG_FRM_ID = f.IVG_FRM_ID inner join CIM_DATA.clc_encntr e on r.clc_encntr_id = e.clc_encntr_id inner join CIM_DATA.cod_svc c on e.COD_SVC_ID = c.COD_SVC_ID inner join IVG_RPT ir on r.IVG_RQST_ID = ir.IVG_RQST_ID

[wwwwb]

SELECT FRM_NAME,SUM(CASE WHEN SVC_NAME='COMMON' THEN IRDAY ELSE 0 END), SUM(CASE WHEN SVC_NAME='TAM' THEN IRDAY ELSE 0 END), SUM(CASE WHEN SVC_NAME='SOCI' THEN IRDAY ELSE 0 END),SUM(IRDAY) AS 总计 FROM ( SELECT f.frm_name,c.svc_name,ir.rpt_rcv_datetime ,r.rqst_datetime, FLOOR(ir.rpt_rcv_datetime- r.rqst_datetime) AS irday FROM IVG_RQST r INNER JOIN IVG_FRM f ON r.IVG_FRM_ID = f.IVG_FRM_ID INNER JOIN CIM_DATA.clc_encntr e ON r.clc_encntr_id = e.clc_encntr_id INNER JOIN CIM_DATA.cod_svc c ON e.COD_SVC_ID = c.COD_SVC_ID INNER JOIN IVG_RPT ir ON r.IVG_RQST_ID = ir.IVG_RQST_ID ) aa GROUP BY FRM_NAME 这样能否运行。最好到ORACLE运行，看看问题出在什么地方 SELECT FRM_NAME,SUM(CASE WHEN SVC_NAME='COMMON' THEN IRDAY ELSE 0 END), SUM(CASE WHEN SVC_NAME='TAM' THEN IRDAY ELSE 0 END), SUM(CASE WHEN SVC_NAME='SOCI' THEN IRDAY ELSE 0 END),SUM(IRDAY) AS 总计 FROM ( SELECT f.frm_name,c.svc_name,ir.rpt_rcv_datetime ,r.rqst_datetime, FLOOR(ir.rpt_rcv_datetime- r.rqst_datetime) AS irday FROM IVG_RQST r INNER JOIN IVG_FRM f ON r.IVG_FRM_ID = f.IVG_FRM_ID INNER JOIN CIM_DATA.clc_encntr e ON r.clc_encntr_id = e.clc_encntr_id INNER JOIN CIM_DATA.cod_svc c ON e.COD_SVC_ID = c.COD_SVC_ID INNER JOIN IVG_RPT ir ON r.IVG_RQST_ID = ir.IVG_RQST_ID ) aa GROUP BY rollup(FRM_NAME); 能否运行

[wwwwb]

可以指定 SELECT FRM_NAME,SUM(CASE WHEN SVC_NAME='COMMON' THEN IRDAY ELSE 0 END) as 你的名称, SUM(CASE WHEN SVC_NAME='TAM' THEN IRDAY ELSE 0 END) as 你的名称, SUM(CASE WHEN SVC_NAME='SOCI' THEN IRDAY ELSE 0 END) as 你的名称,SUM(IRDAY) AS 总计 FROM ( SELECT f.frm_name,c.svc_name,ir.rpt_rcv_datetime ,r.rqst_datetime, FLOOR(ir.rpt_rcv_datetime- r.rqst_datetime) AS irday FROM IVG_RQST r INNER JOIN IVG_FRM f ON r.IVG_FRM_ID = f.IVG_FRM_ID INNER JOIN CIM_DATA.clc_encntr e ON r.clc_encntr_id = e.clc_encntr_id INNER JOIN CIM_DATA.cod_svc c ON e.COD_SVC_ID = c.COD_SVC_ID INNER JOIN IVG_RPT ir ON r.IVG_RQST_ID = ir.IVG_RQST_ID ) aa GROUP BY rollup(FRM_NAME);

[zhangyyy_10]

嗯 还有那个null 应该怎么改成total？ 不是这么指定，可以做成不需要指定的吗？做成活动的列。

[zhangyyy_10]

不过我终于有个东西可以交了，哈哈 现在就差能弄成活的列了

[zhangyyy_10]

SELECT FRM_NAME,SUM(CASE WHEN SVC_NAME='Common Interface Gateway' THEN IRDAY ELSE 0 END), SUM(CASE WHEN SVC_NAME='Social Hygiene Service' THEN IRDAY ELSE 0 END), SUM(CASE WHEN SVC_NAME='Families Clinic Service' THEN IRDAY ELSE 0 END),SUM(IRDAY) AS 总计 FROM ( select f.frm_name,c.svc_name, avg(floor(ir.rpt_rcv_datetime- r.rqst_datetime)) as irday from IVG_RQST r inner join IVG_FRM f on r.IVG_FRM_ID = f.IVG_FRM_ID inner join CIM_DATA.clc_encntr e on r.clc_encntr_id = e.clc_encntr_id inner join CIM_DATA.cod_svc c on e.COD_SVC_ID = c.COD_SVC_ID inner join IVG_RPT ir on r.IVG_RQST_ID = ir.IVG_RQST_ID group by f.frm_name,c.svc_name ) aa GROUP BY rollup(FRM_NAME);

[wwwwb]

嗯 还有那个null 应该怎么改成total？

nvl(FRM_NAME,'total')


动态的要写代码，参考
http://bbs.csdn.net/topics/80169901

[zhangyyy_10]

我的总结： 两种实现方式1：使用union all（不使用子查询）： select f.frm_name, nvl(avg( case when c.svc_name='Common Interface Gateway' then floor(ir.rpt_rcv_datetime- r.rqst_datetime) end ),0) as CommonInterfaceGateway, nvl(avg( case when c.svc_name='Antenatal Service' then floor(ir.rpt_rcv_datetime- r.rqst_datetime ) end ),0) as AntenatalService, nvl(avg( case when c.svc_name='Common Interface Gateway' then floor(ir.rpt_rcv_datetime- r.rqst_datetime) end ),0) + nvl(avg( case when c.svc_name='Antenatal Service' then floor(ir.rpt_rcv_datetime- r.rqst_datetime ) end ),0) as AntenatalService from IVG_RQST r inner join IVG_FRM f on r.IVG_FRM_ID = f.IVG_FRM_ID inner join CIM_DATA.clc_encntr e on r.clc_encntr_id = e.clc_encntr_id inner join CIM_DATA.cod_svc c on e.COD_SVC_ID = c.COD_SVC_ID inner join IVG_RPT ir on r.IVG_RQST_ID = ir.IVG_RQST_ID group by (f.frm_name) union all select 'total' frm_name, sum(case when svc_name='Common Interface Gateway' then irday end) as CommonInterfaceGateway, sum(case when svc_name='Antenatal Service' then irday end) as AntenatalService, sum(irday) as AntenatalService from(select f.frm_name,c.svc_name, avg(floor(ir.rpt_rcv_datetime- r.rqst_datetime)) as irday from IVG_RQST r inner join IVG_FRM f on r.IVG_FRM_ID = f.IVG_FRM_ID inner join CIM_DATA.clc_encntr e on r.clc_encntr_id = e.clc_encntr_id inner join CIM_DATA.cod_svc c on e.COD_SVC_ID = c.COD_SVC_ID inner join IVG_RPT ir on r.IVG_RQST_ID = ir.IVG_RQST_ID group by f.frm_name,c.svc_name); avg( case when c.svc_name='Common Interface Gateway' then floor(ir.rpt_rcv_datetime- r.rqst_datetime) end ) avg( case when c.svc_name='Common Interface Gateway' then floor(ir.rpt_rcv_datetime- r.rqst_datetime) else 0 end ) 这两个语句求的结果不一样，上面是基于c.svc_name='Common Interface Gateway' 和f.frm_name的平均，下面是基于f.frm_name的平均 方法二：这种情况，先使用子查询，查出平均数，然后再求平均数的和。 select nvl(frm_name,'total'),sum(case when svc_name='Common Interface Gateway' then irday end) as CommonInterfaceGateway, sum(case when svc_name='Antenatal Service' then irday end) as AntenatalService, sum(irday) from (select f.frm_name,c.svc_name, avg(floor(ir.rpt_rcv_datetime- r.rqst_datetime)) as irday from IVG_RQST r inner join IVG_FRM f on r.IVG_FRM_ID = f.IVG_FRM_ID inner join CIM_DATA.clc_encntr e on r.clc_encntr_id = e.clc_encntr_id inner join CIM_DATA.cod_svc c on e.COD_SVC_ID = c.COD_SVC_ID inner join IVG_RPT ir on r.IVG_RQST_ID = ir.IVG_RQST_ID group by f.frm_name,c.svc_name) group by rollup(frm_name) ;