SQL2005中利用PIVOT实现分组统计

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

本篇文章中我们将通过具体的实例来讲解PIVOT的用法及功能。

注意：对于从低版本(SQL Server 2000)升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 时，必须将数据库的兼容级别设置为 90 或更高。具体操作可执行下面语句实现：
EXEC sp_dbcmptlevel　 数据库名称　,90

例如：现在有这样一个学生成绩表 [StudentsScore]，其中有学生姓名[Student]、学科[Subject]、成绩[Score]三个字段。（结构及数据如下图）：

image

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

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

  1. declare @Str nvarchar(max)   
  2. set     @str = 'select [Student]'    
  3. select  @str = @str+',['+ Subject + ']' from [StudentsScore] group by [Subject]    
  4. set     @str = @str+' FROM (SELECT [Student],[Score],[Subject] FROM [StudentsScore]) AS T PIVOT(SUM([Score]) FOR [Subject] IN ('  
  5. select  @str = @str+' ['+ Subject + '],' from [StudentsScore] group by [Subject]    
  6. set     @str = left(@str,Len(@str)-1)    
  7. set     @str = @str+ ')) AS thePivot ORDER BY [Student] ASC'  
  8. exec(@str)   

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

  1. declare @Str nvarchar(max)   
  2. set     @str = 'select [Student]'    
  3. select  @str = @str+',['+ Subject + ']' from [StudentsScore] group by [Subject]    
  4. set     @str = @str+',('  
  5. select  @str = @str+'['+ Subject + ']+' from [StudentsScore] group by [Subject]   
  6. set     @str = left(@str,Len(@str)-1)    
  7. set     @str = @str+') AS [总分] FROM (SELECT [Student],[Score],[Subject] FROM [StudentsScore]) AS T PIVOT(SUM([Score]) FOR [Subject] IN ('  
  8. select  @str = @str+' ['+ Subject + '],' from [StudentsScore] group by [Subject]    
  9. set     @str = left(@str,Len(@str)-1)    
  10. set     @str = @str+ ')) AS thePivot ORDER BY [总分] DESC, [Student] ASC'  
  11. exec(@str)   

最终结果如下：

image   image

在程序中可以加上一个打印命令，将实际执行的SQL语句答应出来，如下：

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

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

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

数据表脚本：

  1. /****** 对象:  Table [dbo].[StudentsScore]    脚本日期: 10/29/2009 22:56:18 ******/   
  2. SET ANSI_NULLS ON  
  3. GO   
  4. SET QUOTED_IDENTIFIER ON  
  5. GO   
  6. SET ANSI_PADDING ON  
  7. GO   
  8. CREATE TABLE [dbo].[StudentsScore](   
  9.     [Student] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,   
  10.     [Subject] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,   
  11.     [Score] [int] NULL  
  12. ) ON [PRIMARY]   
  13.   
  14. GO   
  15. SET ANSI_PADDING OFF  

数据记录脚本：

  1. Insert into StudentsScore (Student,Subject,Score) values ( '学生A', '中文', 80 );    
  2. Insert into StudentsScore (Student,Subject,Score) values ( '学生A', '数学', 78 );    
  3. Insert into StudentsScore (Student,Subject,Score) values ( '学生A', '英语', 92 );    
  4. Insert into StudentsScore (Student,Subject,Score) values ( '学生B', '中文', 89 );    
  5. Insert into StudentsScore (Student,Subject,Score) values ( '学生B', '数学', 87 );    
  6. Insert into StudentsScore (Student,Subject,Score) values ( '学生B', '英语', 75 );    
  7. Insert into StudentsScore (Student,Subject,Score) values ( '学生C', '中文', 92 );    
  8. Insert into StudentsScore (Student,Subject,Score) values ( '学生C', '数学', 74 );    
  9. Insert into StudentsScore (Student,Subject,Score) values ( '学生C', '英语', 65 );    
  10. Insert into StudentsScore (Student,Subject,Score) values ( '学生D', '中文', 79 );    
  11. Insert into StudentsScore (Student,Subject,Score) values ( '学生D', '数学', 83 );    
  12. Insert into StudentsScore (Student,Subject,Score) values ( '学生D', '英语', 81 );    
  13. Insert into StudentsScore (Student,Subject,Score) values ( '学生E', '中文', 73 );    
  14. Insert into StudentsScore (Student,Subject,Score) values ( '学生E', '数学', 84 );    
  15. Insert into StudentsScore (Student,Subject,Score) values ( '学生E', '英语', 93 );    
  16. Insert into StudentsScore (Student,Subject,Score) values ( '学生F', '中文', 79 );    
  17. Insert into StudentsScore (Student,Subject,Score) values ( '学生F', '数学', 86 );    
  18. Insert into StudentsScore (Student,Subject,Score) values ( '学生F', '英语', 84 );  
posted @ 2009-10-29 23:10  宜城小子  阅读(6535)  评论(9)  编辑  收藏  举报