在做开发的时候, 排名函数是 sqlserver 经常用到的函数, 在分页的时候需要用, 分组的时候也要用, 主要排名函数有 row-number,rank(),dense-rank(),NTILE()接下来详细说明其使用
1.row-number
作用: 排序, 实现分页
例如:
- SELECT *
- FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY FDate DESC ) AS R ,
- *
- FROM dbo.FI_ProfitAndLoss
- ) AS A
- WHERE R BETWEEN 1 AND 100;
结果如下:
注意: 1, 要使用 over()子句选择对某一列进行排序才能生成序号, 并实现排序.
2,over()子句中的 order by 字段 要与 sql 排序记录中的 order by 字段保持一致, 否则得到的序号可能不是连续的(序号不连续, 比较乱).
2.rank()
作用: 用于返回结果集的分区内每行的排名
例如:
- SELECT RANK() OVER ( ORDER BY FDate DESC ) AS R ,
- FDate
- FROM dbo.FI_ProfitAndLoss;
结果如下:
图中前十条记录的 FDate 相同, 所以他们的序号是一样的, 第 11 条记录的序号就是 11,
注意: 如果第一名和第二名的数据相同, 则并列为第一名, 接下来为第三名, 为分组不连续排名.
3,dense-rank()
作用: 与 rank()基本一样, 但生成的分组排名序号是连续的.
例如:
- SELECT DENSE_RANK() OVER ( ORDER BY FDate DESC ) AS R ,
- FDate
- FROM dbo.FI_ProfitAndLoss;
结果如下
图中前十条记录的 FDate 相同, 所以他们的序号是一样的, 第 11 条记录的序号紧接上一个的序号, 所以为 2 不为 3, 后面的依此类推
4,NTILE()
作用: ntile 函数可以对序号进行分组处理, 将有序分区中的行分发到指定数目的组中. 各个组有编号, 编号从一开始. 对于每一个行, ntile 将返回此行所属的组的编号. 这就相当于将查询出来的记录集放到指定长度的数组中, 每一个数组元素存放一定数量的记录. ntile 函数为每条记录生成的序号就是这条记录所有的数组元素的索引(从 1 开始). 也可以将每一个分配记录的数组元素称为 "桶".ntile 函数有一个参数, 用来指定桶数. 下面的 SQL 语句使用 ntile 函数对 FI_ProfitAndLoss 表进行了装桶处理:
例如:
- SELECT NTILE(6) OVER ( ORDER BY FDate DESC ) AS R ,
- FDate
- FROM dbo.FI_ProfitAndLoss WHERE companyid='264';
结果如下:
如图, 一共 19 条数据, 分 6 组, 第一组 4 条数据, 其他五组都是 3 条数据 .
1, 每组的记录数不能大于它上一组的记录数, 即编号小的桶放的记录数不能小于编号大的桶. 也就是说, 第 1 组中的记录数只能大于等于第 2 组及以后各组中的记录数.
2, 所有组中的记录数要么都相同, 要么从某一个记录较少的组 (命名为 X) 开始后面所有组的记录数都与该组 (X 组) 的记录数相同. 也就是说, 如果有个组, 前三组的记录数都是 9, 而第四组的记录数是 8, 那么第五组和第六组的记录数也必须是 8.
总结:
在使用排名函数的时候需要注意以下三点:
1, 排名函数必须有 OVER 子句.
2, 排名函数必须有包含 ORDER BY 的 OVER 子句.
3, 分组内从 1 开始排序.
来源: http://www.bubuko.com/infodetail-2855691.html