Transact-SQL的四个排序函数.doc

Transact-SQL提供了4个排名函数: RANK(),DENSE_RANK(),ROW_NUMBER(),NTILE()下面是对这4个函数的解释：RANK() 返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。例如，如果两位销售员具有相同的SalesYTD值，则他们将并列第一。由于已有两行排名在前，所以具有下一个最大SalesYTD的销售人员将排名第三。因此，RANK 函数并不总返回连续整数。DENSE_RANK()返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。例如，如果两位顶尖销售员具有相同的 SalesYTD 值，则他们将并列第一。接下来 SalesYTD 最高的销售人员排名第二。该排名等于该行之前的所有行数加一。因此，DENSE_RANK 函数返回的数字没有间断，并且始终具有连续的排名。 ROW_NUMBER()回结果集分区内行的序列号，每个分区的第一行从 1 开始。ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。NTILE()将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将返回此行所属的组的编号。如果分区的行数不能被 integer_expression 整除，则将导致一个成员有两种大小不同的组。按照 OVER 子句指定的顺序，较大的组排在较小的组前面。例如，如果总行数是 53，组数是 5，则前三个组每组包含 11 行，其余两个组每组包含 10 行。另一方面，如果总行数可被组数整除，则行数将在组之间平均分布。例如，如果总行数为 50，有五个组，则每组将包含 10 行。-演示例题，建一个tablecreatetablerankorder(orderidint,qtyint)go-插入数据insertrankordervalues(30,10)insertrankordervalues(10,10)insertrankordervalues(80,10)insertrankordervalues(40,10)insertrankordervalues(30,15)insertrankordervalues(30,20)insertrankordervalues(22,20)insertrankordervalues(21,20)insertrankordervalues(10,30)insertrankordervalues(30,30)insertrankordervalues(40,40)go-查询出各类排名SELECTorderid,qty,ROW_NUMBER()OVER(ORDERBYqty)ASrownumber,RANK()OVER(ORDERBYqty)ASrank,DENSE_RANK()OVER(ORDERBYqty)ASdenserank,NTILE(3)OVER(ORDERBYqty)ASNTILEFROMrankorderORDERBYqty-结果-ROW_NUMBER()是按qty由小到大逐一排名，不并列，排名连续-RANK()是按qty由小到大逐一排名，并列，排名不连续-DENSE_RANK()是按qty由小到大逐一排名，并列，排名连续-NTILE()是按qty由小到大分成3组逐一排名，并列，排名连续orderidqtyrownumberrankdenserankNTILE3010111110102111801031114010411130155522302066322220763221208632103099433030109434040111153sql 2005实现排名非常方便，但是用sql 2000实现排名就比较麻烦，下面是sql 2000的实现代码:-RANK在sql2000中的实现selectorderid,qty,(selectcount(1)+1fromrankorderwhereqtyr.qty)asrankfromrankorderrORDERBYqtygo-ROW_NUMBER在sql2000中的实现-利用临时表和IDENTITY（函数）selectidentity(int,1,1)asROW_NUMBER,orderid,qtyinto#temfromrankorderselectorderid,qty,ROW_NUMBERfrom#temdroptable#temgo-DENSE_RANK在sql2000中的实现selectidentity(int,1,1)asids,qtyinto#tfromrankordergroupbyqtyorderbyqtyselectr.orderid,r.qty,t.idsasDENSE_RANKfromrankorderrjoin#ttonr.qty=t.qtydroptable#tgo排名函数是与窗口函数OVER()配合一起使用的。如果借助OVER子句的参数PARTITION BY,就可以将结果集分为多个分区。排名函数将在每个分区内进行排名.-例题SELECTorderid,qty,DENSE_RANK()OVER(ORDERBYqty)ASa,DENSE_RANK()OVER(PARTITIONBYorderidORDERBYqty)ASbFROMrankorderORDERBYqty-说明：-a列是在全部记录上进行的排名-b列是把orderid中的记录分成了10,21,22,30,40,80这6个区，再在每个区上进行的排名。orderidqtyab101011301011401011801011301522302033212031222031103042303