SQL 序号列ROW_NUMBER,RANK,DENSE_RANK、NTILE-阿里云开发者社区

开发者社区> 数据库> 正文

SQL 序号列ROW_NUMBER,RANK,DENSE_RANK、NTILE

简介: 原文:SQL 序号列ROW_NUMBER,RANK,DENSE_RANK、NTILE SQL 2005新增加相关函数 : ROW_NUMBER,RANK,DENSE_RANK、NTILE 窗口函数 ...
原文:SQL 序号列ROW_NUMBER,RANK,DENSE_RANK、NTILE

SQL 2005新增加相关函数 :
ROW_NUMBER,RANK,DENSE_RANK、NTILE
窗口函数

OVER ( 
       [ <PARTITION BY clause> ]  ----分区列
       [ <ORDER BY clause> ]   ---排序列
       [ <ROW or RANGE clause> ] ---SQL Server 2012 到 SQL Server 2014
       ) 

本文介绍窗口函数与以上四个函数的配合使用

---创建表初始化查询语句
CREATE TABLE YSH
(
  lev1 VARCHAR(10),
  lev2 VARCHAR(10),
  lvalue VARCHAR(10)
)
INSERT INTO YSH (lev1,lev2,lvalue) VALUES ('A','A1','lvalue1')
INSERT INTO YSH (lev1,lev2,lvalue) VALUES ('A','A1','lvalue2')
INSERT INTO YSH (lev1,lev2,lvalue) VALUES ('A','A1','')
INSERT INTO YSH (lev1,lev2,lvalue) VALUES ('A','A2','lvalue1')
INSERT INTO YSH (lev1,lev2,lvalue) VALUES ('A','A2','lvalue2')
INSERT INTO YSH (lev1,lev2,lvalue) VALUES ('B','B1','')
INSERT INTO YSH (lev1,lev2,lvalue) VALUES ('B','B2','lvalueB2')

1、ROW_NUMBER()
PARTITION BY 表示分区,有分区后不同的分区编号都会从1进行编号
ORDER BY 按列排序的方式

SELECT *,
ROW_NUMBER() OVER(ORDER BY lev1,lev2) Lev2NUM,
ROW_NUMBER() OVER(PARTITION BY lev1 ORDER BY lev1,lev2) Lev1NUM,
ROW_NUMBER() OVER(PARTITION BY lev1,lev2 ORDER BY lev1,lev2) Lev1NUM1
FROM YSH

以上查询执行结果无分区时编号依次增长;按lev1分区时,lev1值变更后编号从1开始重新编号;按lev1,lev2组合分区时,两列任何值变更均从新编号
注:此函数的窗口函数中必须存在order by 若不存在则报错。
这里写图片描述

2、RANK() 排名
PARTITION BY 表示分区,有分区后不同的分区编号都会从1进行编号
ORDER BY 按列排序的方式 若值相同则编号也相同。

SELECT *,
RANK() OVER(ORDER BY lev1)
FROM YSH

以上查询结果因为A 有5个相同的值按排名不分前后,但是B的排名在A后面则值为6
这里写图片描述

3、DENSE_RANK() 密集排名

SELECT *,
DENSE_RANK() OVER(ORDER BY lev1)
FROM YSH

以上执行查询结果是相同的排名相同,但是B的排名是紧接着A的编号继续增加的。
这里写图片描述

4、NTILE(int) 平均分组
NTILE(expr) OVER([PARTITION BY]ORDER BY) :把有序的数据集平均分配到 expr指定的数量的桶中,将桶号分配给每一行;如果不能平均分配,则较小桶号的桶分配额外的行,并且各个桶中能放的行数最多相差。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章