原语句如下(其中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