總結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()那樣連續的，但是對于相同值的行