用ROLLUP改写sql

来源：互联网发布：docker mysql默认密码编辑：程序博客网时间：2024/06/06 03:01

原语句如下（其中bb为with()语句里的内容）:

select bb.dept_code,       bb.dept_name,       '0000000001',       '小计',       sum(bb.tot_cost) 金额  from bb group by bb.dept_code, bb.dept_nameUNion allselect 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_nameunion allselect 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_nameunion allselect 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_nameunion allselect 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 BBalter 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