[MSSQL]ROW_NUMBER函数

ROW_NUMBER()在SQL2K5版本中新增,该函数返回结果集分区内行的序列号,每个分区的第一行从 1 开始,连续不间断,后跟OVER BY子句或者PARTITION BY子句

先构造一张表,放一些数据进行,SQL脚本如下

--DROP TABLE T
CREATE TABLE T(GRP_A VARCHAR(20),GRP_B VARCHAR(20),GRP_C VARCHAR(20),VAL INT)
INSERT INTO T(GRP_A,GRP_B,GRP_C,VAL)
SELECT 'a1','b1','c1',10 union all
SELECT 'a1','b1','c2',20 union all
SELECT 'a1','b2','c2',30 union all
SELECT 'a1','b2','c3',40 union all
SELECT 'a1','b2','c3',50 union all
 
SELECT 'a2','b3','c3',12 union all
SELECT 'a2','b3','c3',22 union all
SELECT 'a2','b3','c3',32 
 
SELECT * FROM T

执行查询后的结果:

GRP_A                GRP_B                GRP_C                VAL
-------------------- -------------------- -------------------- -----------
a1                   b1                   c1                   10
a1                   b1                   c2                   20
a1                   b2                   c2                   30
a1                   b2                   c3                   40
a1                   b2                   c3                   50
a2                   b3                   c3                   12
a2                   b3                   c3                   22
a2                   b3                   c3                   32
 
(8 行受影响)

ORDER BY子句

如果单独使用ORDER BY子句,则整个结果集为一个分区,

下边的SQL语句单使用了ORDER BY子句,先按GRP_A排序,然后根据排序后的结果额外生成一连续自增的NUM列

SELECT 
    *,
    ROW_NUMBER()OVER(ORDER BY GRP_A) AS NUM
FROM T

结果集如下,不算复杂啊:

GRP_A                GRP_B                GRP_C                VAL         NUM
-------------------- -------------------- -------------------- ----------- --------------------
a1                   b1                   c1                   10          1
a1                   b1                   c2                   20          2
a1                   b2                   c2                   30          3
a1                   b2                   c3                   40          4
a1                   b2                   c3                   50          5
a2                   b3                   c3                   12          6
a2                   b3                   c3                   22          7
a2                   b3                   c3                   32          8
 
(8 行受影响)

跟不加ROW_NUMBER函数的区别就是增加了最后那一列,NUM,其值是递增的,增量为1

同理可以按照其它字段排序,如VAL,或者GRP_B,GRP_C等,列名不变,列值不变,变的是其它列的顺序

这个查询仅有一个分区,就是整个结果集,整个结果集内有这么一列,自增NUM列,可以用来分页或者啥的啥的

没有什么特别之处,ORDER BY 跟普通的ORDER BY 类似,也可以有多个列的排序,如:

SELECT 
    *,
    ROW_NUMBER()OVER(ORDER BY GRP_A ASC,GRP_B DESC,VAL ASC) AS NUM
FROM T

最终影响的还是结果集中除NUM列外的其它数据的排序

 

PARTITION BY XXX ORDER BY YYY子句

使用PARTITION BY子句后,结果集就会按照该字段进行分区,这时候仍然要使用ORDER BY子句,影响的是分区内的排序,然后在每个分区内生成从1开始的自增列:

SELECT 
    *,
    ROW_NUMBER()OVER(PARTITION BY GRP_A ORDER BY VAL) AS NUM
FROM T

这时候结果集发生变化了,必须得发生!

GRP_A                GRP_B                GRP_C                VAL         NUM
-------------------- -------------------- -------------------- ----------- --------------------
a1                   b1                   c1                   10          1
a1                   b1                   c2                   20          2
a1                   b2                   c2                   30          3
a1                   b2                   c3                   40          4
a1                   b2                   c3                   50          5
a2                   b3                   c3                   12          1
a2                   b3                   c3                   22          2
a2                   b3                   c3                   32          3
 
(8 行受影响)

结果集先是按照GRP_A字段进行了分区:a1分区和a2分区,分别为前五行和后三行,在每个分区内NUM从1递增,增量为1,排序规则为VAL ASC
嗯,很简单吧,
应用场景
比如我们有一张表,该表存储了某件商品的图片,商品与图片是一对多的关系,即一件商品有N件图片,现在要求取图片最多的那个商品
或者每件商品只取按照添加顺序的倒序取前两张图片,这时候就可以用ROW_NUMBER()OVER(PARTITION BY 商品编号 ORDER BY 添加时间 DESC) AS NUM来取
随后在结果集内过滤NUM于2的数据即可!
整体不算复杂,更多的应用场景还是分页时,当我们的自增主键不连续时,要取前20条数据,就可以利用该函数特性生成连续递增的NUM列,再BETWEEN即可
分页示例 
SELECT * FROM (SELECT 
    *,
    ROW_NUMBER()OVER(ORDER BY VAL) AS NUM
FROM T) AS SOMETABLENAME
WHERE NUM BETWEEN 1 AND 5


相对应的结果集 

GRP_A                GRP_B                GRP_C                VAL         NUM
-------------------- -------------------- -------------------- ----------- --------------------
a1                   b1                   c1                   10          1
a2                   b3                   c3                   12          2
a1                   b1                   c2                   20          3
a2                   b3                   c3                   22          4
a1                   b2                   c2                   30          5
 
(5 行受影响)
仅返回了前五行
 
posted @ 2011-08-12 13:23  kkun  阅读(8242)  评论(0)  编辑  收藏  举报