34,576
社区成员
发帖
与我相关
我的任务
分享
select case when (grouping(name)=1) then '合计'
when (grouping(name)=0) And (grouping(class)=1) Then '小计'
Else [name]
end as [name],
IsNULL (class,''),sum(sorce),grouping([name])
from aaa
group by [name],class WITH RoLLUP
王五 語文 95
王五 数学 83
小计 178
張三 語文 95
張三 数学 80
小计 175
李四 語文 95
李四 数学 95
小计 190
合计 543
select AA.*
from
(
select * from aa
union all
select name,class='小计',sum(sorce) as sorce from aa group by name
) AA
order by name,(select case class when '小计' then 99 end)
方法二再稍微改进下
select [name] = (case when grouping(name) = 0 and grouping(class) = 1 then '小计'
when grouping(name) = 1 and grouping(class) = 1 then '总计'
else [name] end)
,class = (case when grouping(name) = 0 and grouping(class) = 1 then cast(sum(sorce)as nvarchar(20))
when grouping(name) = 1 and grouping(class) = 1 then cast(sum(sorce)as nvarchar(20))
else class end)
,score = (case when grouping(name) = 1 or grouping(class) = 1 then null else sum(sorce) end)
from aa a
group by name,class with rollup
create table aa (name varchar(10),class varchar(10),sorce int)
insert aa select '张三','数学','80'
union all select '张三','语文','95'
union all select '李四','语文','95'
union all select '李四','数学','95'
union all select '王五','数学','83'
union all select '王五','语文','95'
go
方法一
select
name = (case when name is null then '总计' when class is null then'小计' else name end)
,class =isnull(class,sm)
,sm = isnull(class,null)
from
(
select name,class,sm = sum(sorce)
from aa
group by name,class with rollup
)t
方法二
select [name] = (case when grouping(name) = 0 and grouping(class) = 1 then '小计'
when grouping(name) = 1 and grouping(class) = 1 then '总计'
else [name] end)
,class = (case when grouping(name) = 0 and grouping(class) = 1 then cast((select sum(sorce)from aa where name = a.name )as nvarchar(20))
when grouping(name) = 1 and grouping(class) = 1 then cast((select sum(sorce)from aa)as nvarchar(20))
else class end)
,score = (case when grouping(name) = 1 or grouping(class) = 1 then null else sum(sorce) end)
from aa a
group by name,class with rollup
name class score
---------- -------------------- -----------
李四 数学 95
李四 语文 95
小计 190 NULL
王五 数学 83
王五 语文 95
小计 178 NULL
张三 数学 80
张三 语文 95
小计 175 NULL
总计 543 NULL
(10 row(s) affected)
create table aa(name varchar(10),class varchar(10),score int)
insert aa select '张三','数学','80'
union all select '张三','语文','95'
union all select '李四','语文','95'
union all select '李四','数学','95'
union all select '王五','数学','83'
union all select '王五','语文','95'
select name , class , score from
(
select * , name id from aa
union all
select '小计' name , '' class , sum(score) score , name id from aa group by name
union all
select name = '合计', class = '' , sum(score) score , '' id from aa
) t
order by case id when '' then 2 else 1 end ,
id ,
case class when '' then 2 else 1 end
drop table aa
/*
name class score
---------- ---------- -----------
李四 语文 95
李四 数学 95
小计 190
王五 数学 83
王五 语文 95
小计 178
张三 数学 80
张三 语文 95
小计 175
合计 543
(所影响的行数为 10 行)
*/