FROM http://blog.csdn.net/jgmydsai/article/details/10598559
原语句如下(其中bb为with()语句里的内容):
- select bb.dept_code,
- bb.dept_name,
- '0000000001',
- '小计',
- sum(bb.tot_cost) 金额
- from bb
- group by bb.dept_code, bb.dept_name
- UNion all
- select bb.dept_code,
- bb.dept_name,
- '0000011111',
- '1',
- sum(bb.tot_cost)
- from bb
- where (bb.code = '001' or bb.code = '002' OR bb.code = '003')
- group by bb.dept_code, bb.dept_name
- union all
- select bb.dept_code,
- bb.dept_name,
- bb.code,
- bb.fee_stat_name,
- sum(bb.tot_cost)
- from bb
- where (bb.code = '001' or bb.code = '002' OR bb.code = '003')
- group by bb.dept_code, bb.dept_name, bb.code, bb.fee_stat_name
- union all
- select bb.dept_code, bb.dept_name, '01111111', '2', sum(bb.tot_cost)
- from bb
- where (bb.code <> '001' and bb.code <> '002' and bb.code <> '003')
- group by bb.dept_code, bb.dept_name
- union all
- select bb.dept_code,
- bb.dept_name,
- bb.fee_stat_cate,
- bb.fee_stat_name,
- sum(bb.tot_cost)
- from bb
- where (bb.code <> '001' AND bb.code <> '002' AND bb.code <> '003')
- group by bb.dept_code,
- bb.dept_name,
- bb.fee_stat_cate,
- bb.fee_stat_cate,
- bb.fee_stat_name
首先对bb处理如下,实际上就是改写下with语句
- ALTER TABLE BB ADD tt VARCHAR2(50)
- UPDATE BB SET tt = (CASE
- WHEN (BB.CODE = '001' OR BB.CODE = '002' OR BB.CODE = '003') THEN
- '1'
- WHEN (BB.CODE <> '001' AND BB.CODE <> '002' AND
- BB.CODE <> '003') THEN
- '2'
- END)
- update BB set code = fee_stat_cate where tt = '2'
- SELECT * FROM BB
- alter table bb drop column fee_stat_cate
改写后的语句如下
- SELECT BB.DEPT_CODE ,
- BB.DEPT_NAME ,
- CASE
- WHEN GROUPING(CODE) = 1 AND GROUPING(tt) = 1 THEN
- '0000000001'
- WHEN GROUPING(CODE) = 1 AND tt = '1' THEN
- '0000011111'
- WHEN GROUPING(CODE) = 1 AND tt = '2' THEN
- '01111111'
- ELSE
- BB.CODE
- END AS CODE,
- CASE
- WHEN GROUPING(CODE) = 1 AND GROUPING(tt) = 1 THEN
- '小计'
- WHEN GROUPING(CODE) = 1 THEN
- tt
- ELSE
- BB.FEE_STAT_NAME
- END AS fy,
- SUM(BB.TOT_COST) 金额
- FROM BB
- GROUP BY DEPT_CODE,
- DEPT_NAME,
- ROLLUP(tt, ( CODE, FEE_STAT_NAME))
- ORDER BY GROUPING(tt) DESC,
- tt,
- GROUPING(CODE) DESC,
- 3,
- 4,
- 2