本文标签：Sql Server2005 t-sql 排名函数
Sql Server2005中新增加了4个排名函数：ROW_NUMBER, RANK, DENSE_RANK, NTILE；大家一定已经对ROW_NUMBER非常熟悉了，所以我从最后一个NTILE开始分析 。
NTILE在msdn中的解释是：将有序分区中的行分发到指定数目的组中 。各个组有编号，编号从一开始 。对于每一个行，NTILE 将返回此行所属的组的编号 。不知道大家是不是一下子就能看懂这个解释，反正我是结合解释自己写了例子才弄明白的 。
准备脚本，我们创建一个简单的3列表，三列分别是id，categoryId，和name，如下：
GO
if object_id(t_ntile,U) is not null
drop table t_ntile;
GO
create table t_ntile
(
id int unique not null,
categoryId int not null,
name nvarchar(20)
)
go
INSERT INTO t_ntile VALUES(1,1,A)
INSERT INTO t_ntile VALUES(2,4,B)
INSERT INTO t_ntile VALUES(3,2,C)
INSERT INTO t_ntile VALUES(4,1,D)
INSERT INTO t_ntile VALUES(5,3,E)
INSERT INTO t_ntile VALUES(6,3,F)
INSERT INTO t_ntile VALUES(7,2,G)
INSERT INTO t_ntile VALUES(8,2,H)
INSERT INTO t_ntile VALUES(9,2,I)
Go
|
查询语句如下：
SELECT id,categoryId,name
,ntile value = NTILE(3) OVER(PARTITION BY categoryId ORDER BY categoryId)
FROM t_ntile
|
我们给NTITL传的参数是3，即表示每一区将分成三组，然后OVER中表达式指定要根据categoryId来分割分组，并要按照categoryId排序 。上面的表达式执行结果如下：
 |
| 图1（原图经51CTO.com修改） |
（如需查看原图请点击：图一原图）
图一说明：根据题中给出条件得出：结果集中的所有行都将被按照categoryId分隔成区，每一区都被分成3组（如上图） 。
categoryId为1的两行：id=1行被分配到组1，id=4行被分配到组2，第一分区此时已分配完毕 。
categoryId为2的四行：id=7，id=8行被分配到组1，id=9行被分配到组2，id=3行被分配到组3，第二分区已分配完毕 。
categoryId为3的两行：id=5行被分配到组1，id=6行被分配到组2，第三分区分配完毕 。
categoryId为4的一行：id=2行被分配到组1，第四分区分配完毕 。
至此结果集中的所有行均已被分配 。各组成员如上图所示 。
下面看RANK和DENSE_RANK这对兄弟函数，这对函数要比NTITL容易理解一些 。MSDN对RANK的解释：返回结果集的分区内每行的排名 。行的排名是相关行之前的排名数加一 。MSDN上对DENSE_RANK的解释是：返回结果集分区中行的排名，在排名中没有任何间断 。行的排名等于所讨论行之前的所有排名数加一 。下面我用一个例子来说明一下,用结果说明他们的差别：
if object_id(student_class_grade,U) is not null
drop table student_class_grade;
GO
create table student_class_grade
(
student_id int, --学生id
class_no int, --班级编号
grade int --成绩
);
GO
INSERT INTO student_class_grade VALUES(1,1,90);
INSERT INTO student_class_grade VALUES(2,1,85);
INSERT INTO student_class_grade VALUES(3,1,80);
INSERT INTO student_class_grade VALUES(4,1,80);
INSERT INTO student_class_grade VALUES(5,1,90);
INSERT INTO student_class_grade VALUES(6,1,75);
INSERT INTO student_class_grade VALUES(7,1,89); INSERT INTO student_class_grade VALUES(11,2,90);
INSERT INTO student_class_grade VALUES(12,2,85);
INSERT INTO student_class_grade VALUES(13,2,80);
INSERT INTO student_class_grade VALUES(14,2,80);
INSERT INTO student_class_grade VALUES(15,2,90);
INSERT INTO student_class_grade VALUES(16,2,75);
INSERT INTO student_class_grade VALUES(17,2,89);
GO
--显示各个班级学生的成绩排名
SELECT student_id
,class_no,grade
,名次 = RANK() OVER(PARTITION BY class_no ORDER BY grade desc)
FROM student_class_grade
GO
SELECT student_id
,class_no,grade
,名次 = DENSE_RANK() OVER(PARTITION BY class_no ORDER BY grade desc)
FROM student_class_grade
|
分别执行下面两个select脚本,可以得到如下的结果
 |
| 图1 |
可以看到1班同学的排名依次是1,1,3,4有了并列第一之后第二名的排序就是3了 。
如下是DENSE_RANK的执行结果：
 |
| 图1 |
可以看到排名依次是1,1,2,3 … 当出现两个并列第一之后，第二名的排名是2，而非RANK中的3.所以我们在给学生成绩排名时可以用DENSE_RANK而不是RANK 。
最后要介绍的是ROW_NUMBER这个函数为我们分页提供了便利 。我们可以结合CTE（通用表表达式）使用，如下例子
WITH CTE_rn (student_id,class_no,grade,rn) AS(
SELECT student_id,class_no,grade,rn = ROW_NUMBER() OVER(ORDER BY student_id ASC)
FROM student_class_grade
WHERE 0=0 --可以在此处加一些过滤条件，这样下面的分页的sql中就都不需要加条件了
)
--获得第-10条的数据
SELECT student_id,class_no,grade FROM CTE_rn WHERE rn BETWEEN 6 AND 10;
SELECT totalCn = COUNT(*) FROM student_class_grade WHERE 0=0
|
|
