SQL2005使用PIVOT实现分组统计__StudentsScore_Subject_Student_Score_str_

当前位置: 数据库>sqlserver

SQL2005使用PIVOT实现分组统计

来源: 互联网发布时间：2014-08-29

本文导语: PIVOT 是SQL Server 2005中新加入的运算符，PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。通过具体的实例来讲解PIVOT的用法及功能。注意：...

PIVOT 是SQL Server 2005中新加入的运算符，PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。

通过具体的实例来讲解PIVOT的用法及功能。

注意：对于从低版本(SQL Server 2000)升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 时，必须将数据库的兼容级别设置为 90 或更高。具体操作可执行下面语句实现：
EXEC sp_dbcmptlevel　数据库名称　,90
例如：现在有这样一个学生成绩表 [StudentsScore]，其中有学生姓名[Student]、学科[Subject]、成绩[Score]三个字段。（结构及数据如下图）：

要求：按学生姓名[Student]显示各科成绩，并按姓名升序排列。

在传统应用中，可以通过SELECT...CASE语句达到目的，但是使用SQL Server 2005中新加入的PIVOT运算符将会使代码更简单、更具可读性。
实现代码：

代码示例:

declare @Str nvarchar(max)   

set     @str = 'select [Student]'    

select  @str = @str+',['+ Subject + ']' from [StudentsScore] group by [Subject]    

set     @str = @str+' 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 [Student] ASC'  

exec(@str)

如果在此基础上还要求统计出总分，并按总分降序排列，只需稍稍做点改动就可以了，代码如下：

代码示例:

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语句答应出来，如下：

代码示例:

select [Student],[数学],[英语],[中文],([数学]+[英语]+[中文]) AS 总分 FROM (SELECT [Student],[Score],[Subject] FROM [StudentsScore]) AS T PIVOT(sum([Score]) FOR [Subject] IN ( [数学], [英语], [中文])) AS thePivot ORDER BY [Student]  

这样一来，在列数已知的情况下，就可以直接构造类似的语句，而不需要定义变量。

下面是本文中用到的数据表及数据记录的SQL，方便大家测试。

数据表脚本：

代码示例:

/****** 对象:  Table [dbo].[StudentsScore]    脚本日期: 10/29/2009 22:56:18 ******/   

SET ANSI_NULLS ON  

GO   

SET QUOTED_IDENTIFIER ON  

GO   

SET ANSI_PADDING ON  

GO   

CREATE TABLE [dbo].[StudentsScore](   

    [Student] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,   

    [Subject] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,   

    [Score] [int] NULL  

) ON [PRIMARY]   

GO   

SET ANSI_PADDING OFF

数据记录脚本：

代码示例:

Insert into StudentsScore (Student,Subject,Score) values ( '学生A', '中文', 80 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生A', '数学', 78 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生A', '英语', 92 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生B', '中文', 89 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生B', '数学', 87 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生B', '英语', 75 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生C', '中文', 92 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生C', '数学', 74 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生C', '英语', 65 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生D', '中文', 79 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生D', '数学', 83 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生D', '英语', 81 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生E', '中文', 73 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生E', '数学', 84 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生E', '英语', 93 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生F', '中文', 79 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生F', '数学', 86 );    

Insert into StudentsScore (Student,Subject,Score) values ( '学生F', '英语', 84 );