我有一组数据结果最后想实现下面的效果,请问如何实现呢?
A B C D E 汇总
------------------------------------------------------------------------------------------------
1 2 3 4 5 15
1 2 3 4 5 15
1 2 3 4 5 15
1 2 3 4 5 15
-------------------------------------------------------------------------------------------------
4 8 12 16 20 60也就是最后一列和最后一行都是行列汇总数据。谢谢了。
A B C D E 汇总
------------------------------------------------------------------------------------------------
1 2 3 4 5 15
1 2 3 4 5 15
1 2 3 4 5 15
1 2 3 4 5 15
-------------------------------------------------------------------------------------------------
4 8 12 16 20 60也就是最后一列和最后一行都是行列汇总数据。谢谢了。
union all
select sum(a) , sum(b),sum(c),sum(d),sum(e),sum(汇总) from tb
insert into tb values(1 ,2 ,3 ,4 ,5 ,15)
insert into tb values(1 ,2 ,3 ,4 ,5 ,15)
insert into tb values(1 ,2 ,3 ,4 ,5 ,15)
insert into tb values(1 ,2 ,3 ,4 ,5 ,15)
goselect * from tb
union all
select sum(a) , sum(b),sum(c),sum(d),sum(e),sum(汇总) from tbdrop table tb/*
A B C D E 汇总
----------- ----------- ----------- ----------- ----------- -----------
1 2 3 4 5 15
1 2 3 4 5 15
1 2 3 4 5 15
1 2 3 4 5 15
4 8 12 16 20 60(所影响的行数为 5 行)
*/
create table tb(A int,B int,C int,D int,E int)
insert into tb values(1 ,2 ,3 ,4 ,5 )
insert into tb values(1 ,2 ,3 ,4 ,5 )
insert into tb values(1 ,2 ,3 ,4 ,5 )
insert into tb values(1 ,2 ,3 ,4 ,5 )
goselect *,汇总=a+b+c+d+e from tb
union all
select sum(a) , sum(b),sum(c),sum(d),sum(e),sum(a+b+c+d+e) from tbdrop table tb
/*
A B C D E 汇总
----------- ----------- ----------- ----------- ----------- -----------
1 2 3 4 5 15
1 2 3 4 5 15
1 2 3 4 5 15
1 2 3 4 5 15
4 8 12 16 20 60
这个是我中间过程得到的数据,最后一行、最后一列数据库内本来没有的,
是通过计算最后得到的汇总行列数据啊。
insert into tb values(1 ,2 ,3 ,4 ,5 ,15)
insert into tb values(1 ,2 ,3 ,4 ,5 ,15)
insert into tb values(1 ,2 ,3 ,4 ,5 ,15)
insert into tb values(1 ,2 ,3 ,4 ,5 ,15)
goselect a,b,c,d,e,a+b+c+d+e 汇总 from tb
union all
select sum(a) , sum(b),sum(c),sum(d),sum(e),sum(a+b+c+d+e) from tbdrop table tb/*
A B C D E 汇总
----------- ----------- ----------- ----------- ----------- -----------
1 2 3 4 5 15
1 2 3 4 5 15
1 2 3 4 5 15
1 2 3 4 5 15
4 8 12 16 20 60(所影响的行数为 5 行)
*/
use tempdb;
/*
create table t1
(
A int not null,
B int not null,
C int not null,
D int not null,
E int not null
);
insert into t1(A,B,C,D,E)
values
(1,2,3,4,5),
(1,2,3,4,5),
(1,2,3,4,5),
(1,2,3,4,5);
*/
select
t1.A,t1.B,t1.C,t1.D,t1.E,
(t1.A + t1.B + t1.C + t1.D + t1.E) as [小计]
from t1
union all
select
SUM(t1.A),SUM(t1.B),SUM(t1.C),SUM(t1.D),SUM(t1.E),
(SUM(t1.A) + SUM(t1.B) + SUM(t1.C) + SUM(t1.D) + SUM(t1.E))
from t1
group by t1.A,t1.B,t1.C,t1.D,t1.E;
use tempdb;
/*
create table t1
(
A int not null,
B int not null,
C int not null,
D int not null,
E int not null
);
insert into t1(A,B,C,D,E)
values
(1,2,3,4,5),
(1,2,3,4,5),
(1,2,3,4,5),
(1,2,3,4,5);
*/
select
t1.A,t1.B,t1.C,t1.D,t1.E,
(t1.A + t1.B + t1.C + t1.D + t1.E) as [汇总]
from t1
union all
select
SUM(t1.A),SUM(t1.B),SUM(t1.C),SUM(t1.D),SUM(t1.E),
(SUM(t1.A) + SUM(t1.B) + SUM(t1.C) + SUM(t1.D) + SUM(t1.E))
from t1
group by t1.A,t1.B,t1.C,t1.D,t1.E;