Sql Server2005对t-sql的增强之排名函数

Sql Server2005中新增加了4个排名函数：ROW_NUMBER, RANK, DENSE_RANK, NTILE；大家一定已经对ROW_NUMBER非常熟悉了，所以我从最后一个NTILE开始分析。

NTILE在msdn中的解释是：将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将返回此行所属的组的编号。不知道大家是不是一下子就能看懂这个解释，反正我是结合解释自己写了例子才弄明白的。

准备脚本，我们创建一个简单的3列表，三列分别是id，categoryId，和name，如下：

 

----------------------------分割线-------------------------------

下面看RANK和DENSE_RANK这对兄弟函数，这对函数要比NTITL容易理解一些。MSDN对RANK的解释：返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。MSDN上对DENSE_RANK的解释是：返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。下面我用一个例子来说明一下,用结果说明他们的差别：

select脚本,可以得到如下的结果

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

分别执行下面两个

 

可以看到1班同学的排名依次是1,1,3,4有了并列第一之后第二名的排序就是3了。

如下是DENSE_RANK的执行结果：

 

 

可以看到排名依次是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

ROW_NUMBER函数可以在取每个分类的前n条记录时很有用。
例如:
create table student(
id int not null,
name varchar(20),
grade int,
class int --班级
)
GO
--以下select语句返回每班级前3名的学生
WITH student_rn AS
(select id,name,class,grade, 排名 =ROW_NUMBER() OVER(PARTITION BY class ORDER BY grade DESC) FROM student)
SELECT id,name,class,grade, 排名 FROM student_rn
WHERE 排名 <= 3
全文结束。

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排序。上面的表达式执行结果如下：