请教一个简单的SQL语句
已知数据：
NO NAME SCORE
1 张三 70
2 李四 90
3 王五 80
4 赵六 100
5 周二 60
6 丁一 80
需要按行分组统计分数，
按照一行分组(@COUNT=1)，则数据结果：
NO NAME SCORE
1 张三 70
2 合计 70
3 李四 90
4 合计 90
5 王五 80
6 合计 80
7 赵六 100
8 合计 100
9 周二 60
10 合计 60
11 丁一 80
12 合计 80
按照两行分组(@COUNT=2)，则数据结果：
NO NAME SCORE
1 张三 70
2 李四 90
3 合计 160
4 王五 80
5 赵六 100
6 合计 180
7 周二 60
8 丁一 80
9 合计 140
如何用一条语句实现，谢谢大家帮助！
[最优解释]
declare @count int=3
select row_number() over(order by getdate()) as no,name,score
from (
select ceiling(no/(@count*1.0)) no,case when grouping(name)=1 then '合计' else name end as name,sum(score) score
from #A
group by ceiling(no/(@count*1.0)),name with rollup
) as TB
终极版了 ...
[其他解释]
create table #A (no int,name varchar(20),score int)
insert into #A
select 1 ,'张三', 70 union all select
2 ,'李四', 90 union all select
3 ,'王五', 80 union all select
4 ,'赵六', 100 union all select
5 ,'周二', 60 union all select
6 ,'丁一', 80
--测试
declare @count int=2
select row_number() over(order by getdate()) as no,name,score
from (
select ceiling((no+1)/@count) no,case when grouping(name)=1 then '合计' else name end as name,sum(score) score
from #A
group by ceiling((no+1)/@count),name with rollup
) as TB
[其他解释]
declare @count int=1
select row_number() over(order by no) as no,name,score
from (
select no/@count no,case when grouping(name)=1 then '合计' else name end as name,sum(score) score
from tablename
group by no/@count,name with rollup) as TB
[其他解释]
