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 行）
*/