编程论坛 → SQL Server论坛

求sql查询语句

slash1984 发布于 2007-05-12 12:12， 504 次点击

表a如下：
aa bb
a 100
b 200
a 100
a 50
b 100
b 90
c 50
c 40
.
.
.

怎样写sql语句输出如下结果
aa bb
a 50
a 100
a 100
合计 250
b 90
b 100
b 200
合计 390
.
.
.
尽量用一句分组语句。

3 回复

棉花糖ONE2007-05-13 12:46

找帮助with rollup

slash19842007-05-13 19:12

select case when(bb=bb) then aa
WHEN (GROUPING(aa) = 1) THEN '总计'
else '合计' end as aa,
sum(num) as bb
from 表名
group by aa,bb
with rollup

棉花糖ONE2007-05-13 20:37

declare @a table(aa varchar(1),bb int)
insert into @a select 'a',100
union all select 'b',200
union all select 'a',100
union all select 'a',50
union all select 'b',100
union all select 'b',90
union all select 'c',50
union all select 'c',40

select aa=case when grouping(bb)=1 then '合计' else aa end,sum(bb) as bb from @a group by aa,bb with rollup having grouping(aa)<>1