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

----------------------------分割线-------------------------------
下面看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,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
来自 http://www.cnblogs.com/yukaizhao/archive/2008/04/28/sql_server_feature_rank_function.html