日期:2014-05-17 浏览次数:20884 次
select id,type,area,num1,num2,num3 from 你的表 union select null,null,null,sum(num1),sum(num2),sum(num3) from 你的表
------解决方案--------------------
如果要用rollup 要添加子查询过滤条件了 一步完成不了吧 空值可以自己转换成合计
with t1 as ( select 11 c1,'a' c2,'aa' c3,3 c4,8 c5,5 c6 from dual union all select 13 c1,'b' c2,'bb' c3,1 c4,5 c5,1 c6 from dual union all select 14 c1,'b' c2,'cc' c3,3 c4,4 c5,4 c6 from dual ) select * from ( select c1,c2,c3,sum(c4) c4,sum(c5) c5,sum(c6) c6 from t1 group by rollup(c1,c2,c3) ) t1 where (c2 is not null and c3 is not null) or c1 is null c1 c2 c3 c4 c5 c6 ------------------------------------------------------ 1 11 a aa 3 8 5 2 13 b bb 1 5 1 3 14 b cc 3 4 4 4 7 17 10
------解决方案--------------------
WITH CTE AS ( SELECT 11 id,'A' type,'AA' area,3 num1,8 num2,5 num3 FROM DUAL UNION ALL SELECT 13, 'B', 'BB', 1, 5, 1 FROM DUAL UNION ALL SELECT 14, 'B', 'CC', 3, 4, 4 FROM DUAL UNION ALL SELECT 15, 'A', 'AA', 3, 3, 5 FROM DUAL UNION ALL SELECT 16, 'C', 'CC', 2, 4, 8 FROM DUAL UNION ALL SELECT 17, 'C', 'DD', 3, 1, 6 FROM DUAL UNION ALL SELECT 18, 'D', 'DD', 5, 4, 7 FROM DUAL UNION ALL SELECT 19, 'D', 'BB', 3, 4, 3 FROM DUAL ) SELECT N.ID, NVL(N.type,M.TYPE) TYPE,AREA, NVL(NUM1,M.A) NUM1, NVL(NUM2,M.B) NUM2, NVL(NUM3,M.C) NUM3 FROM CTE N FULL JOIN (SELECT 'TOTAL' TYPE, SUM(NUM1) A,SUM(NUM2) B,SUM(NUM3) C FROM CTE) M ON N.TYPE=M.TYPE 结果如下: 19 D BB 3 4 3 18 D DD 5 4 7 17 C DD 3 1 6 16 C CC 2 4 8 15 A AA 3 3 5 14 B CC 3 4 4 13 B BB 1 5 1 11 A AA 3 8 5 TOTAL 23 33 39