oracle数据库实现汇总报表的方法

--------备忘录

在常用的汇总报表中，会出现通过某些条件实现分组后，对数量金额进行小计以及总计

可以使用group by rollup(col1,col2,....coln) 来实现分组统计

若要对总计，小计等汉字的显示进行控制，可以使用case()函数

select
tp.tp_xmbh as xmid,
tp.tp_ccbh as showid,
(case when tp.tp_xmbh is null then null else (select wp.name from ZYPWT2.PM_PROJECT_INFO wp where STATUS=1 and PI_CATEGORY='2101'and id=tp.tp_xmbh) end) as col1,
(case when tp.tp_xmbh is null then null else (select to_char(wp.pi_show_start_time,'yyyy-mm-dd')|| '-' ||to_char(wp.pi_show_end_time,'yyyy-mm-dd') from ZYPWT2.PM_PROJECT_INFO wp where STATUS=1 and PI_CATEGORY='2101'and id=tp.tp_xmbh) end) as col2,
(case when tp.tp_ccbh is null then null else (select ws.name from zypwt2.pm_show_info ws where ws.status=1 and ws.id= tp.tp_ccbh) end) as col3,
(case when tp.tp_ccbh is null then null else (select to_char(ws.si_show_start_time,'yyyy-mm-dd hh24:mi') from zypwt2.pm_show_info ws where ws.status=1 and ws.id= tp.tp_ccbh) end ) as col4,
(case when tp.tp_ccbh is null then null else (select ven.name from zypwt2.vm_venues_info ven where ven.status='1' and ven.id=(select ws.si_venue_id from zypwt2.pm_show_info ws where ws.status=1 and ws.id=tp.tp_ccbh)) end) as col5,
(case
when tp.tp_pjid is null and tp.tp_ccbh is null and tp.tp_xmbh is not null then '项目总计'
when tp.tp_pjid is null and tp.tp_ccbh is not null then '场次小计'
when tp.tp_pjid is null and tp.tp_ccbh is null and tp.tp_xmbh is null then '总计'
else to_char((select pri.pli_price from zypwt2.pm_price_level_info pri where pri.id=tp.tp_pjid and status='1')) end
) as col6,
sum(tp.tp_sqzs) as col7,
'￥'||sum(tp.tp_sqje) as col8,
sum(tp.tp_tpzs) as col9,
'￥'||sum(tp.tp_tpje) as col10
from crm_dd_tpxx tp
where tp.tp_khbh='decc16ad7f6747b3bf24e5ee379c6b94' and zt='1' group by rollup(tp.tp_xmbh,tp.tp_ccbh,tp.tp_pjid) order by tp.tp_xmbh,tp.tp_ccbh