怎样在一个表中,实现如下统计页面
如表:test a b c d 合计 汇总
z 2 3 5 3 13 13
s 1 2 3 2 8 21
d 3 2 4 2 11 32
f 2 3 7 6 18 50
合 8 10 19 13 50 50
如何实现上面的统计页面,合计:是每行数据的求合,汇总:是当前行以上,合计列数据的求和,合:是每列数据的求合
请高手指点如何实现, 谢谢了
如表:test a b c d 合计 汇总
z 2 3 5 3 13 13
s 1 2 3 2 8 21
d 3 2 4 2 11 32
f 2 3 7 6 18 50
合 8 10 19 13 50 50
如何实现上面的统计页面,合计:是每行数据的求合,汇总:是当前行以上,合计列数据的求和,合:是每列数据的求合
请高手指点如何实现, 谢谢了
a b c d
2 3 5 3
1 2 3 2
3 2 4 2
2 3 7 6 这样的吗?
SQL> select * from test1; A B C D
---------- ---------- ---------- ----------
2 3 5 3
1 2 3 2
3 2 4 2
2 3 7 6SQL> with tb as (select a,b,c,d,sum(a+b+c+d) heji,sum(a+b+c+d) over(order by c asc,a desc ) huizong from test1 group by a,b,c,d)
2 select * from tb
3 union
4 select sum(a),sum(b),sum(c),sum(d),sum(heji),sum(huizong) from tb; A B C D HEJI HUIZONG
---------- ---------- ---------- ---------- ---------- ----------
1 2 3 2 8 8
2 3 5 3 13 32
2 3 7 6 18 50
3 2 4 2 11 19
8 10 19 13 50 109SQL> 要想弄成你自己的效果,就自己改红色字体部分,这排序一点规律都没有
只有用union
就算用group by cube 、rollup都没办法with tmp as
(select 'a' type,1 a,1 b,1 c from dual
union all
select 'b' type,1 a,2 b,3 c from dual
union all
select 'c' type,2 a,3 b,4 c from dual
)
select type,a,b,c,a+b+c,sum(a+b+c) over(order by type)
from tmp
union all
select '合计',sum(a),sum(b),sum(c),sum(a+b+c),sum(a+b+c)
from tmp
;
SQL很累了,何必强求在一条SQL里面实现呢?
除非是完成作业。
col varchar2(20),
a number(18,0),
b number(18,0),
c number(18,0),
d number(18,0),
合计 number(18,0),
汇总 number(18,0)
);insert into test(col,a,b,c,d,合计,汇总) values('z', 2, 3, 5, 3, 13, 13);
insert into test(col,a,b,c,d,合计,汇总) values('s', 1, 2, 3, 2, 8, 21);
insert into test(col,a,b,c,d,合计,汇总) values('d', 3, 2, 4, 2, 11, 32);
insert into test(col,a,b,c,d,合计,汇总) values('f', 2, 3, 7, 6, 18, 50);scott@SZTYORA> select col, sum(value)
2 from tmp1
3 group by rollup(col);select col, sum(a) sum_a, sum(b) sum_b, sum(c) sum_c, sum(d) sum_d,
sum(合计) sum_合计, sum(汇总) sum_汇总
from test
group by rollup(col);