总结SQL Server窗口函数、排名函数的简单使用

总结SQLServer窗口函数、排名函数的简单使用前言：我一直十分喜欢使用SQLServer2005/2008的窗口函数，排名函数ROW_NUMBER()尤甚。今天晚上我在查看SQLServer开发的相关文档，整理收藏夹发现了两篇收藏已久的好文，后知后觉，读后又有点收获，顺便再总结一下。一、从一个熟悉的示例说起我们熟知的数据库分页查询，以这一篇介绍过的为例吧。分页查询Person表中的人，可以这么写SQL语句：?123456789101112131415161718192021WITHRecordAS(

SELECT

Row_Number()OVER(ORDERBYIdDESC)ASRecordNumber,

Id,

FirstName,

LastName,

Height,

Weight

FROM

Person(NOLOCK)

)

SELECT

RecordNumber,

(SELECTCOUNT(0)FROMRecord)ASTotalCount,

Id,

FirstName,

LastName,

Height,

Weight

FROMRecord

WHERERecordNumberBETWEEN1AND10其中，ROW_NUMBER()是排名函数，而紧随其后的OVER()函数就是窗口函数。你还在用二次top方式的分页查询吗？可以考虑尝试使用排名函数配合CTE实现分页。

二、窗口函数本文介绍窗口函数，以下面的学生成绩表为例：?12345678CREATETABLE[StudentScore](

[Id][int]IDENTITY(1,1)NOTNULL,

[StudentId][int]NOTNULLCONSTRAINT[DF_StudentScore_StudentId]

DEFAULT((0)),

[ClassId][int]NOTNULLCONSTRAINT[DF_StudentScore_ClassId]

DEFAULT((0)),

[CourseId][int]NOTNULLCONSTRAINT[DF_StudentScore_CourseId]

DEFAULT((0)),

[Score][float]NOTNULLCONSTRAINT[DF_StudentScore_Score]

DEFAULT((0)),

[CreateDate][datetime]NOTNULLCONSTRAINT[DF_StudentScore_CreateDate]

DEFAULT(getdate()))ON[PRIMARY]其中，Id是自增Id，CreateDate是录入时间，StudentId学生，ClassId班级，CourseId

课程,Score

分数。录入一些测试数据如下：?1234567891011121314151617181920212223242526272829303132333435363738394041424344--CourseId2:语文4：数学8：英语

--1班学生成绩INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(1,1,2,85)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(2,1,2,95.5)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(3,1,2,90)

INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(1,1,4,90)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(2,1,4,98)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(3,1,4,89)

INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(1,1,8,80)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(2,1,8,75.5)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(3,1,8,77)

--2班学生成绩INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(1,2,2,90)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(2,2,2,77)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(3,2,2,78)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(4,2,2,83)

INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(1,2,4,98)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(2,2,4,95)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(3,2,4,78)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(4,2,4,100)

INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(1,2,8,85)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(2,2,8,90)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(3,2,8,86)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(4,2,8,78.5)

--3班学生成绩INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(1,3,2,82)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(2,3,2,78)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(3,3,2,91)

INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(1,3,4,83)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(2,3,4,78)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(3,3,4,99)

INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(1,3,8,86)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(2,3,8,78)INSERTINTOStudentScore(StudentId,ClassId,CourseId,Score)VALUES(3,3,8,97)窗口函数是SQLServer2005新增的函数。下面就谈谈它的基本概念：1、窗口函数的作用窗口函数是对一组值进行操作，不需要使用GROUPBY子句对数据进行分组，还能够在同一行中同时返回基础行的列和聚合列。举例来说，我们要得到一个年级所有班级所有学生的平均分，按照传统的写法，我们肯定是通过AVG聚合函数来实现求平均分。这样带来的”坏处“是我们不能轻松地返回基础行的列（班级，学生等列），而只能得到聚合列。因为聚合函数的要点就是对一组值进行聚合，以GROUPBY查询作为操作的上下文，由于GROUPBY操作对数据进行分组后，查询为每个组只返回一行数据，因此，要限制所有表达式为每个组只返回一个值。而通过窗口函数，基础列和聚合列的查询都轻而易举。2、基本语法OVER([PARTITIONBYvalue_expression,..[n]]<ORDERBYBY_Clause>)

窗口函数使用OVER函数实现，OVER函数分带参和不带参两种。其中可选参数PARTITIONBY用于将数据按照特定字段分组。3、简单示例查询学生成绩表的基本列以及所有班级所有学生的语文平均分：?1234567891011SELECT

--Id,

--CreateDate,

StudentId,

ClassId,

CourseId,

Score,

CAST(AVG(Score)OVER()ASdecimal(5,2))AS

'语文平均分'FROM

StudentScore

WHERECourseId=2结果如下：4、PARTITIONBY如果我们需要查询每一个班级的语文平均分，可以根据PARTIONBY来进行分组：?1234567891011SELECT

--Id,

--CreateDate,

StudentId,

ClassId,

CourseId,

Score,

CAST(AVG(Score)OVER(PARTITIONBYClassId)ASdecimal(5,2))AS

'语文平均分'FROM

StudentScore

WHERECourseId=2查询结果如下：三个班级的语文平均分是不同的。到这里，其实你可能已经体会到使用OVER函数的好处了：a、OVER子句的优点就是能够在返回基本列的同时，在同一行对它们进行聚合

b、可以在表达式中混合使用基本列和聚合列如果我们使用传统的GROUPBY分组查询，直接获取基本列和聚合列就不是这么简单一句SQL了。如你所知，我们知道的很多聚合函数，如SUM,AVG,MAX,MIN等聚合函数都支持窗口函数的运算。

二、让人爱不释手的排名函数SQLServer提供了4个排名函数:ROW_NUMBER(),RANK(),DENSE_RANK()和NTILE()。下面通过示例重点谈谈这四个函数的使用。1、ROW_NUMBER()返回结果集分区内行的序列号，每个分区的第一行从1开始。ORDERBY子句可确定在特定分区中为行分配唯一ROW_NUMBER的顺序。下面的查询按照数学成绩逆序排列：?1234567891011SELECT

Id,--CreateDate,

ROW_NUMBER()OVER(ORDERBYScoreDESC)AS'序号',

StudentId,

ClassId,

CourseId,

ScoreFROM

StudentScore

WHERECourseId=8结果如下：据我所知，此函数在SQLServer分页查询中几乎已经普及应用。Goodjob。

2、RANK()和DENSE_RANK()（1）、RANK()函数返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。?1234567891011SELECT

Id,--

CreateDate,

RANK()OVER(ORDERBYScoreDESC)AS'序号',

StudentId,

ClassId,

CourseId,

ScoreFROM

StudentScore

WHERECourseId=8结果如下：注意，它和ROW_NUMBER()的异同点，您应该已经知道了：a、RANK函数和ROW_NUMBER函数类似，它们都是用来对结果进行排序。

b、不同的是，ROW_NUMBER函数为每一个值生成唯一的序号，而RANK函数为相同的值生成相同的序号。

上图中，两个86分的学生对应的序号都是3,而接着排在它们下面的序号直接变成了5。（2）、DENSE_RANK()函数返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。?1234567891011SELECT

Id,--

CreateDate,

DENSE_RANK()OVER(ORDERBYScoreDESC)AS'序号',

StudentId,

ClassId,

CourseId,

ScoreFROM

StudentScore

WHERECourseId=8查询结果如下：上图中，两个86分的学生对应的序号都是3,而接着排在它们下面的序号是4（也就是说DENSE_RANK()函数查询的序号是类似ROW_NUMBER()那样连续的，但是对于相同值的行