请教一个简单的SQL语句

[dstm0001]

已知数据：
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=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

[發糞塗牆]

引用 1 楼 lixzhong 的回复:

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……

2 的时候实现不了哦

[开启时代]

纠正下： declare @count int=1 select row_number() over(order by no) as no,name,score from ( select no/(@count+1) no,case when grouping(name)=1 then '合计' else name end as name,sum(score) score from tablename group by no/(@count+1),name with rollup) as TB

[dstm0001]

引用 5 楼 lixzhong 的回复:

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 ……

谢谢，按照3行，4行，5行，6行汇总的时候结果也不对啊

[开启时代]

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 终极版了 ...

[dstm0001]

这个不错,谢谢了

[dstm0001]

3楼的好像还不对啊，哪位大侠指导一下

[开启时代]

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