加载中…SQL的ROW_NUMBER()OVER生成序号与分页
(2018-12-28 15:20:56)
标签：
sqlrow_number()over |
分类： 代码天地 |
一 需求说明
比如下表tb，虽然id 是自增唯一的，但是由于增删改查，导致id也不连续，假如要按年龄排
序，id的顺序也就乱了，所以就需要一个按某种规则排序后的唯一升序序号，这就需要使用
ROW_NUMBER()函数
id name sex age
1 n1 1 25
4 n2 0 18
7 n5 1 21
8 n4 0 28
9 n3 1 36
二 任意列排序后加入序号
ROW_NUMBER() OVER([PARTITION BY col1, col2]
ORDER BY col3, col4)
PARTITION BY：表示分组 ,按字段
col1, col2进行分组，这部分可有可无
ORDER BY：表示排序,按字段
col3, col4 进行排序
例如 按年龄升序后加入序号no
id name sex age no
4 n2 0 18 1
7 n5 1 21 2
1 n1 1 25 3
8 n4 0 28 4
9 n3 1 36 5
三 分页
WHERE no>2
id name sex age no
1 n1 1 25 3
8 n4 0 28 4
四 内外层查询条件执行顺序
1 把 sex=1 写在外层查询
WHERE no>0 and sex=1
id name sex age no
7 n5 1 21 2
1 n1 1 25 3
9 n3 1 36 5
得到的序号从2开始，而不是从1开始，中间的4号也丢了
2 把 sex=1 写在内层查询
WHERE no>0
id name sex age no
7 n5 1 21 1
1 n1 1 25 2
9 n3 1 36 3
得到的序号从1开始，而且也连续了
3 顺序说明
从上面的例子中得知，内层查询的WHERE 先执行，而后执行外层查询的WHERE
4 尽量把条件放到内层执行,最外层只限定序号
这样再获得排序后的男性第2页为
WHERE no>2
id name sex age no
9 n3 1 36 3
五 获得满足条件的记录数
要想分页，首先需要知道总的记录数record_count，然后除以每页的记录数pageSize，来获得总的页数。
当然 可以选择其中一个字段来获得记录的总数，SELECT id FROM tb WHERE sex=1
，但若记录十分庞大，它会返回海量数据导致系统没有反映，因此可以使用 count。
SELECT COUNT(*) as record_count FROM tb WHERE
sex=1 ORDER BY age
返回以下错误信息：
消息 8127，级别 16，状态 1，第 1 行
ORDER BY 子句中的列 "tb.age"
无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
SELECT COUNT(*) as record_count ,max(age) as age FROM tb WHERE sex=1 ORDER BY
age
返回正确的结果
record_count age
3 36
4 也可以分到下层中
SELECT COUNT(*) as record_count FROM ( SELECT
* FROM tb WHERE sex=1 ORDER BY age ) AS res_set
返回以下错误信息：
消息 1033，级别 15，状态 1，第 1 行
除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY
子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
5 在子查询中再加上 SELECT TOP
100 PERCENT
SELECT COUNT(*) as record_count FROM ( SELECT
TOP 100 PERCENT * FROM tb WHERE sex=1 ORDER BY
age ) AS res_set
返回正确的结果
record_count
3
六 通用分页的格式为
SELECT TOP pageSize * FROM
WHERE no>pageSize*(pageIndex-1)
喜欢
0
赠金笔