分类统计（急）
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
我想以Ｂ分类统计a1,a2,a3,a4并求a1,a2,a3,a4得到
１ ４ ８ １２ １６ ４０
２ ４ ８ １２ １６ ４０
8 16 24 32 80
[解决办法]
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
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create 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 rollup
drop 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 行）
*/
