B a1 a2 a3 a4
1 1 2 3 4
1 1 2 3 4
1 1 2 3 4
1 1 2 3 4
2 1 2 3 4
2 2 2 3 4
2 1 2 3 4
2 1 2 3 4我想以B分类统计a1,a2,a3,a4并求a1,a2,a3,a4得到
1 4 8 12 16 40
2 4 8 12 16 40
8 16 24 32 80
1 1 2 3 4
1 1 2 3 4
1 1 2 3 4
1 1 2 3 4
2 1 2 3 4
2 2 2 3 4
2 1 2 3 4
2 1 2 3 4我想以B分类统计a1,a2,a3,a4并求a1,a2,a3,a4得到
1 4 8 12 16 40
2 4 8 12 16 40
8 16 24 32 80
1 1 2 3 4
1 1 2 3 4
1 1 2 3 4
1 1 2 3 4
2 1 2 3 4
2 2 2 3 4
2 1 2 3 4
2 1 2 3 4我想以B分类统计a1,a2,a3,a4并求a1,a2,a3,a4的和,想得到如下结果
1 4 8 12 16 40
2 4 8 12 16 40
8 16 24 32 80
B,
SUM(a1) As a1,
SUM(a2) As a2,
SUM(a3) As a3,
SUM(a4) As a4
From TEST
Group By B
With RollUp
(B Int,
a1 Int,
a2 Int,
a3 Int,
a4 Int)
Insert TEST Select 1, 1, 2, 3, 4
Union All Select 1, 1, 2, 3, 4
Union All Select 1, 1, 2, 3, 4
Union All Select 1, 1, 2, 3, 4
Union All Select 2, 1, 2, 3, 4
Union All Select 2, 2, 2, 3, 4
Union All Select 2, 1, 2, 3, 4
Union All Select 2, 1, 2, 3, 4
GO
Select
B,
SUM(a1) As a1,
SUM(a2) As a2,
SUM(a3) As a3,
SUM(a4) As a4
From TEST
Group By B
With RollUp
GO
Drop Table TEST
--Result
/*
B a1 a2 a3 a4
1 4 8 12 16
2 5 8 12 16
NULL 9 16 24 32
*/
(B Int,
a1 Int,
a2 Int,
a3 Int,
a4 Int)
Insert TEST Select 1, 1, 2, 3, 4
Union All Select 1, 1, 2, 3, 4
Union All Select 1, 1, 2, 3, 4
Union All Select 1, 1, 2, 3, 4
Union All Select 2, 1, 2, 3, 4
Union All Select 2, 2, 2, 3, 4
Union All Select 2, 1, 2, 3, 4
Union All Select 2, 1, 2, 3, 4
GO
Select
B,
SUM(a1) As a1,
SUM(a2) As a2,
SUM(a3) As a3,
SUM(a4) As a4,
SUM(a1 + a2 + a3 + a4) As a
From TEST
Group By B
With RollUp
GO
Drop Table TEST
--Result
/*
B a1 a2 a3 a4 a
1 4 8 12 16 40
2 5 8 12 16 41
NULL 9 16 24 32 81
*/
drop table tb
gocreate table tb(B int,a1 int,a2 int,a3 int,a4 int)insert into tb(B,a1,a2,a3,a4) values(1, 1, 2, 3, 4)
insert into tb(B,a1,a2,a3,a4) values(1, 1, 2, 3, 4)
insert into tb(B,a1,a2,a3,a4) values(1, 1, 2, 3, 4)
insert into tb(B,a1,a2,a3,a4) values(1, 1, 2, 3, 4)
insert into tb(B,a1,a2,a3,a4) values(2, 1, 2, 3, 4)
insert into tb(B,a1,a2,a3,a4) values(2, 2, 2, 3, 4)
insert into tb(B,a1,a2,a3,a4) values(2, 1, 2, 3, 4)
insert into tb(B,a1,a2,a3,a4) values(2, 1, 2, 3, 4)select isnull(cast(b as varchar), '合计') b,sum(a1) a1,sum(a2) a2,sum(a3) a3,sum(a4) a4 ,sum(a1+a2+a3+a4) 合计 from tb group by b with rollupdrop table tb/*
b a1 a2 a3 a4 合计
----- ----------- ----------- ----------- ----------- -----------
1 4 8 12 16 40
2 5 8 12 16 41
合计 9 16 24 32 81(所影响的行数为 3 行)
*/