Create table tb([No] nvarchar(250),[Name] nvarchar(250),[RefResult] int,[Subject] nvarchar(250),[Result] int)
Insert tb
select N'01',N'张三',249,N'科目1',74 union all
select N'01',N'张三',249,N'科目2',83 union all
select N'01',N'张三',249,N'科目3',93 union all
select N'02',N'李四',258,N'科目1',74 union all
select N'02',N'李四',258,N'科目2',84 union all
select N'02',N'李四',258,N'科目3',94
要求结果
/*
No Name 参考总成绩 科目1 科目2 科目3 总成绩 成绩差异
01 张三 249 74 83 93 250 -1
02 李四 258 74 84 94 252 6
合计 507 148 167 187 502 5
*/
备注：1.每个学生的参考总成绩都是唯一的，比如张三都是249，李四 258
2.成绩差异=参考总成绩-总成绩
------解决思路----------------------
小调一下,忘记给NO取别名了。
with cte as (
select [No],[Name],
max([RefResult]) as 参考总成绩,
sum(case when [Subject]='科目1' then [Result] else 0 end) as 科目1,
sum(case when [Subject]='科目2' then [Result] else 0 end) as 科目2,
sum(case when [Subject]='科目3' then [Result] else 0 end) as 科目3,
sum([Result]) as 总成绩,
max([RefResult])-sum([Result]) as 成绩差异
from tb
group by [No],[Name]
)
select isnull(No,'') as No,Name,参考总成绩,科目1,科目2,科目3,总成绩,成绩差异 from (
select * from cte
union all
select null,'合计',SUM(参考总成绩),SUM(科目1),SUM(科目2),SUM(科目3),SUM(总成绩),SUM(成绩差异)
from cte)a
order by isnull(No,'999')
------解决思路----------------------
declare @sql nvarchar(4000)加个order by就可以了
set @sql=''
Select @sql=@sql+','+[Subject]+'=sum(case when [Subject]='''+RTRIM([Subject])+''' then [Result] else 0 end)'
FROM tb
group BY [Subject],[Sub_no] ORDER BY [Sub_no]
PRINT @sql
exec('select [No]=isnull([No],''''),[Name]=isnull([Name],N''合计'')'+
@sql+',[总成绩]=sum([Result]) from tb group by [No],[Name] with rollup
having grouping([No])=1 or grouping([Name])=0 order by grouping([No]) asc,[总成绩] desc')
你试下