name num ID(主键这里不写值了)
A 1
A 2
B 3
B 4
怎么样在分组NAME统计后得到如下的结果呢name num
sum 10
A 3
B 7注意这里的SUM结果是全表的总和我知道可以如下写
select name,num
form table
group by name
union all
select 'sum' as sum,sum(num)
from table
因为我的table可是复杂的SQL得出来的不想在去执行一下,所以能不能在一次的FROM之后得到上面的结果呢
from tb
group by name with rollup
go
create table [tb]([name] varchar(10),[num] int)
insert [tb] select 'A',1
union all select 'A',2
union all select 'B',3
union all select 'B',4
goselect
case when name is null then 'sum' else name end name,
sum(num) num
from tb
group by name
with rollup
order by case when name is null then 0 else 1 end
/*
name num
---------- -----------
sum 10
A 3
B 7(3 行受影响)
*/
declare @a table (name varchar(50),num int)
insert @a
select N'A',3 union all
select N'A',1 union all
select N'B',6 union all
select N'B',7
select isnull(name,'sum') name,sum(num) num
from @a group by name with rollup