總結(jié)SQL Server窗口函數(shù)、排名函數(shù)的簡(jiǎn)單使用

總結(jié)SQLServer窗口函數(shù)、排名函數(shù)的簡(jiǎn)單使用前言：我一直十分喜歡使用SQLServer2005/2008的窗口函數(shù)，排名函數(shù)ROW_NUMBER()尤甚。今天晚上我在查看SQLServer開發(fā)的相關(guān)文檔，整理收藏夾發(fā)現(xiàn)了兩篇收藏已久的好文，后知后覺，讀后又有點(diǎn)收獲，順便再總結(jié)一下。一、從一個(gè)熟悉的示例說起我們熟知的數(shù)據(jù)庫(kù)分頁查詢，以這一篇介紹過的為例吧。分頁查詢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()是排名函數(shù)，而緊隨其后的OVER()函數(shù)就是窗口函數(shù)。你還在用二次top方式的分頁查詢嗎？可以考慮嘗試使用排名函數(shù)配合CTE實(shí)現(xiàn)分頁。

二、窗口函數(shù)本文介紹窗口函數(shù)，以下面的學(xué)生成績(jī)表為例：?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是錄入時(shí)間，StudentId學(xué)生，ClassId班級(jí)，CourseId

課程,Score

分?jǐn)?shù)。錄入一些測(cè)試數(shù)據(jù)如下：?1234567891011121314151617181920212223242526272829303132333435363738394041424344--CourseId2:語文4：數(shù)學(xué)8：英語

--1班學(xué)生成績(jī)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班學(xué)生成績(jī)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班學(xué)生成績(jī)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)窗口函數(shù)是SQLServer2005新增的函數(shù)。下面就談?wù)勊幕靖拍睿?、窗口函數(shù)的作用窗口函數(shù)是對(duì)一組值進(jìn)行操作，不需要使用GROUPBY子句對(duì)數(shù)據(jù)進(jìn)行分組，還能夠在同一行中同時(shí)返回基礎(chǔ)行的列和聚合列。舉例來說，我們要得到一個(gè)年級(jí)所有班級(jí)所有學(xué)生的平均分，按照傳統(tǒng)的寫法，我們肯定是通過AVG聚合函數(shù)來實(shí)現(xiàn)求平均分。這樣帶來的”壞處“是我們不能輕松地返回基礎(chǔ)行的列（班級(jí)，學(xué)生等列），而只能得到聚合列。因?yàn)榫酆虾瘮?shù)的要點(diǎn)就是對(duì)一組值進(jìn)行聚合，以GROUPBY查詢作為操作的上下文，由于GROUPBY操作對(duì)數(shù)據(jù)進(jìn)行分組后，查詢?yōu)槊總€(gè)組只返回一行數(shù)據(jù)，因此，要限制所有表達(dá)式為每個(gè)組只返回一個(gè)值。而通過窗口函數(shù)，基礎(chǔ)列和聚合列的查詢都輕而易舉。2、基本語法OVER([PARTITIONBYvalue_expression,..[n]]<ORDERBYBY_Clause>)

窗口函數(shù)使用OVER函數(shù)實(shí)現(xiàn)，OVER函數(shù)分帶參和不帶參兩種。其中可選參數(shù)PARTITIONBY用于將數(shù)據(jù)按照特定字段分組。3、簡(jiǎn)單示例查詢學(xué)生成績(jī)表的基本列以及所有班級(jí)所有學(xué)生的語文平均分：?1234567891011SELECT

--Id,

--CreateDate,

StudentId,

ClassId,

CourseId,

Score,

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

'語文平均分'FROM

StudentScore

WHERECourseId=2結(jié)果如下：4、PARTITIONBY如果我們需要查詢每一個(gè)班級(jí)的語文平均分，可以根據(jù)PARTIONBY來進(jìn)行分組：?1234567891011SELECT

--Id,

--CreateDate,

StudentId,

ClassId,

CourseId,

Score,

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

'語文平均分'FROM

StudentScore

WHERECourseId=2查詢結(jié)果如下：三個(gè)班級(jí)的語文平均分是不同的。到這里，其實(shí)你可能已經(jīng)體會(huì)到使用OVER函數(shù)的好處了：a、OVER子句的優(yōu)點(diǎn)就是能夠在返回基本列的同時(shí)，在同一行對(duì)它們進(jìn)行聚合

b、可以在表達(dá)式中混合使用基本列和聚合列如果我們使用傳統(tǒng)的GROUPBY分組查詢，直接獲取基本列和聚合列就不是這么簡(jiǎn)單一句SQL了。如你所知，我們知道的很多聚合函數(shù)，如SUM,AVG,MAX,MIN等聚合函數(shù)都支持窗口函數(shù)的運(yùn)算。

二、讓人愛不釋手的排名函數(shù)SQLServer提供了4個(gè)排名函數(shù):ROW_NUMBER(),RANK(),DENSE_RANK()和NTILE()。下面通過示例重點(diǎn)談?wù)勥@四個(gè)函數(shù)的使用。1、ROW_NUMBER()返回結(jié)果集分區(qū)內(nèi)行的序列號(hào)，每個(gè)分區(qū)的第一行從1開始。ORDERBY子句可確定在特定分區(qū)中為行分配唯一ROW_NUMBER的順序。下面的查詢按照數(shù)學(xué)成績(jī)逆序排列：?1234567891011SELECT

Id,--CreateDate,

ROW_NUMBER()OVER(ORDERBYScoreDESC)AS'序號(hào)',

StudentId,

ClassId,

CourseId,

ScoreFROM

StudentScore

WHERECourseId=8結(jié)果如下：據(jù)我所知，此函數(shù)在SQLServer分頁查詢中幾乎已經(jīng)普及應(yīng)用。Goodjob。

2、RANK()和DENSE_RANK()（1）、RANK()函數(shù)返回結(jié)果集的分區(qū)內(nèi)每行的排名。行的排名是相關(guān)行之前的排名數(shù)加一。如果兩個(gè)或多個(gè)行與一個(gè)排名關(guān)聯(lián)，則每個(gè)關(guān)聯(lián)行將得到相同的排名。?1234567891011SELECT

Id,--

CreateDate,

RANK()OVER(ORDERBYScoreDESC)AS'序號(hào)',

StudentId,

ClassId,

CourseId,

ScoreFROM

StudentScore

WHERECourseId=8結(jié)果如下：注意，它和ROW_NUMBER()的異同點(diǎn)，您應(yīng)該已經(jīng)知道了：a、RANK函數(shù)和ROW_NUMBER函數(shù)類似，它們都是用來對(duì)結(jié)果進(jìn)行排序。

b、不同的是，ROW_NUMBER函數(shù)為每一個(gè)值生成唯一的序號(hào)，而RANK函數(shù)為相同的值生成相同的序號(hào)。

上圖中，兩個(gè)86分的學(xué)生對(duì)應(yīng)的序號(hào)都是3,而接著排在它們下面的序號(hào)直接變成了5。（2）、DENSE_RANK()函數(shù)返回結(jié)果集分區(qū)中行的排名，在排名中沒有任何間斷。行的排名等于所討論行之前的所有排名數(shù)加一。如果有兩個(gè)或多個(gè)行受同一個(gè)分區(qū)中排名的約束，則每個(gè)約束行將接收相同的排名。?1234567891011SELECT

Id,--

CreateDate,

DENSE_RANK()OVER(ORDERBYScoreDESC)AS'序號(hào)',

StudentId,

ClassId,

CourseId,

ScoreFROM

StudentScore

WHERECourseId=8查詢結(jié)果如下：上圖中，兩個(gè)86分的學(xué)生對(duì)應(yīng)的序號(hào)都是3,而接著排在它們下面的序號(hào)是4（也就是說DENSE_RANK()函數(shù)查詢的序號(hào)是類似ROW_NUMBER()那樣連續(xù)的，但是對(duì)于相同值的行