数据表A
id type area num1 num2 num3
11 A AA 3 8 5
13 B BB 1 5 1
14 B CC 3 4 4
15 A AA 3 3 5
16 C CC 2 4 8
17 C DD 3 1 6
18 D DD 5 4 7
19 D BB 3 4 3
要的结果是这样的
id type area num1 num2 num3
11 A AA 3 8 5
13 B BB 1 5 1
14 B CC 3 4 4
15 A AA 3 3 5
16 C CC 2 4 8
17 C DD 3 1 6
18 D DD 5 4 7
19 D BB 3 4 3
Total 23 33 39
求大神指教，我用rollup不行啊，rollup会对每种类型都分开统计，而我不需要类型分开
谢谢
------解决方案--------------------
- SQL code
select id,type,area,num1,num2,num3 from 你的表union select null,null,null,sum(num1),sum(num2),sum(num3)from 你的表
------解决方案--------------------
如果要用rollup 要添加子查询过滤条件了 一步完成不了吧 空值可以自己转换成合计
- SQL code
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)) t1where (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 52 13 b bb 1 5 13 14 b cc 3 4 44 7 17 10
------解决方案--------------------
- SQL code
WITH CTE AS(SELECT 11 id,'A' type,'AA' area,3 num1,8 num2,5 num3 FROM DUALUNION ALLSELECT 13, 'B', 'BB', 1, 5, 1 FROM DUAL UNION ALLSELECT 14, 'B', 'CC', 3, 4, 4 FROM DUAL UNION ALLSELECT 15, 'A', 'AA', 3, 3, 5 FROM DUAL UNION ALLSELECT 16, 'C', 'CC', 2, 4, 8 FROM DUAL UNION ALLSELECT 17, 'C', 'DD', 3, 1, 6 FROM DUAL UNION ALLSELECT 18, 'D', 'DD', 5, 4, 7 FROM DUAL UNION ALLSELECT 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 318 D DD 5 4 717 C DD 3 1 616 C CC 2 4 815 A AA 3 3 514 B CC 3 4 413 B BB 1 5 111 A AA 3 8 5 TOTAL 23 33 39