DB2 小计 合计实验

在测试库上，新建了 t表，具体数据见表：

(1):SQL:

SELECT * FROM (
Select CS,CASE WHEN (GROUPING(HP)=1) THEN '小计 '
ELSE HP END as 货品,SUM(ZL) as 重量
From t
GROUP BY CS, HP WITH ROLLUP ) AS WWW ORDER BY CS ASC,货品 asc ;

以后继续探索 。。。。

 

(2):ERP里面这样实现的：

with temp(wlxxid,ssnf_zj,ssjd_zj,zkl) as (select wlxxid,ssnf_zj,ssjd_zj,max(zkl)
from ies.t_zhzkl_zj where wldwxxid='0917' and state='审核通过'  group by wlxxid,ssnf_zj,ssjd_zj) 
select substr(wlmc,1,30) wlmc,c.cphh,'【'||c.cphh||'】小计' as sxms,sum(coalesce(xssl,0)) xssl,coalesce(hslsj,0)*coalesce(f.zkl,0) hslsj,
 sum(coalesce(xssl,0)*coalesce(hslsj,0)*coalesce(f.zkl,0)) xsje,'' bz 
 from db2admin.t_fz_xssbmx a
 left join db2admin.t_fz_xssb b on a.xssbxxid=b.xssbxxid 
 left join ies.t_cpsxzh c on a.sxms=c.cpsxzhid 
 left join temp f on c.cpxxid=f.wlxxid and c.ssnf_zj=f.ssnf_zj and c.ssjd_zj=f.ssjd_zj  
 left join ies.t_wlxx d on c.cpxxid=d.wlxxid 
 left join ies.t_xsjg e on c.cphh=e.cphh_zj and e.state<>'失效'  where  b.wldwxxid='0917' and b.kwxxid = '091701'
  and b.lrrq>='2011-11-29'  and b.lrrq<'2011-12-06' 
  group by wlmc,c.cphh,hslsj,f.zkl
 
union all  select substr(wlmc,1,30) wlmc,c.cphh,c.sxms as sxms,coalesce(xssl,0) xssl,coalesce(hslsj,0)*coalesce(f.zkl,0) hslsj,
coalesce(xssl,0)*coalesce(hslsj,0)*coalesce(f.zkl,0) xsje,'' bz 
from db2admin.t_fz_xssbmx a
left join db2admin.t_fz_xssb b on a.xssbxxid=b.xssbxxid 
left join ies.t_cpsxzh c on a.sxms=c.cpsxzhid 
left join temp f on c.cpxxid=f.wlxxid and c.ssnf_zj=f.ssnf_zj and c.ssjd_zj=f.ssjd_zj 
left join ies.t_wlxx d on c.cpxxid=d.wlxxid 
left join ies.t_xsjg e on c.cphh=e.cphh_zj and e.state<>'失效'  where  b.wldwxxid='0917' and b.kwxxid = '091701' 
and b.lrrq>='2011-11-29'  and b.lrrq<'2011-12-06'  order by wlmc,cphh with ur;