SQL2005中利用PIVOT实现分组统计
2009-10-30 00:00:00 来源:WEB开发网核心提示: 代码是不是很简单,如果在此基础上还要求统计出总分,SQL2005中利用PIVOT实现分组统计(2),并按总分降序排列,只需稍稍做点改动就可以了,代码如下:declare@Strnvarchar(max)set@str='select[Student]'select@str=@st
代码是不是很简单,如果在此基础上还要求统计出总分,并按总分降序排列,只需稍稍做点改动就可以了,代码如下:
declare @Str nvarchar(max)
set @str = 'select [Student]'
select @str = @str+',['+ Subject + ']' from [StudentsScore] group by [Subject]
set @str = @str+',('
select @str = @str+'['+ Subject + ']+' from [StudentsScore] group by [Subject]
set @str = left(@str,Len(@str)-1)
set @str = @str+') AS [总分] FROM (SELECT [Student],[Score],[Subject] FROM [StudentsScore]) AS T PIVOT(SUM([Score]) FOR [Subject] IN ('
select @str = @str+' ['+ Subject + '],' from [StudentsScore] group by [Subject]
set @str = left(@str,Len(@str)-1)
set @str = @str+ ')) AS thePivot ORDER BY [总分] DESC, [Student] ASC'
exec(@str)
最终结果如下:
在程序中可以加上一个打印命令,将实际执行的SQL语句答应出来,如下:
[]
更多精彩
赞助商链接