SQL奇技淫巧(01):给查出的数据排序编个号【row_number() over(order by c)】(mysql,db2,oracle,sqlserver通用)
我们天天都在跟数据库打交道,写下的代码不计其数,写下的SQL更是可以绕地球几圈。这里收集关于SQL的神奇语法及用法,虽然你可能没有用过,但这些SQL却可以在关键的时候,派上用场。
我对SQL语句的理解,可以比作一座桥梁,将零散的数据组合起来,拿到我所需要的有效信息。也以此记录一下使用心得
一. 语法及基础用法
注意: row_number()不能单独使用,需要和over(order by col)一起使用。
语法1:
row_number() over(ORDER BY col)
意思:简单的说row_number()从1开始,为每一条分组记录返回一个数字,这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序,再为降序以后的每条xlh记录返回一个序号。
语法2:
row_number() over(PARTITION BY col1 ORDER BY col2)
意思:表示根据col1分组,在分组内部根据 col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)
关于Parttion by:
Parttion by关键字是SQL中分析性函数的一部分,用于给结果集进行分区。它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列,能够返回一个分组中的多条记录(记录数不变),而Group by是对原始数据进行聚合统计,一般只有一条反映统计值的结果(每组返回一条)。
Parttion by 后面如果想按多个字段分组,则以逗号分隔。
row_number() over(PARTITION BY col1,col3,col4 ORDER BY col2)
实例:
(1) 建表造数据:
create table employee (empid int ,deptid int ,salary decimal(10,2));
insert into employee values(1,10,5500.00);
insert into employee values(2,10,4500.00);
insert into employee values(3,20,1900.00);
insert into employee values(4,20,4800.00);
insert into employee values(5,40,6500.00);
insert into employee values(6,40,14500.00);
insert into employee values(7,40,44500.00);
insert into employee values(8,50,6500.00);
insert into employee values(9,50,7500.00);
数据显示为:
(2) 需求:根据部门分组,显示每个部门的工资等级
sql:
SELECT *,
row_number() over(PARTITION by deptid ORDER BY salary desc) as score
FROM
employee
预期结果:
二. 真实案例
通过上面基础语法和使用,我们也只是简单地知道row_number()可以用来编号排序使用,以及分组内排序两种情景。通过对其有一个了解之后,你的心中充满疑问, 如果对于在项目中,我们又可以拿它来做什么,又可以解决什么样的问题。
这个语法也是我最近工作内容的时候,才接触到的,之前也只是模模糊糊地停留在知道有这个,而在工作中没实际用过它。
上面介绍的是在单表中的一个操作,那么对于稍微复杂的多表关联操作是否有考虑过
下面举个贴近身边的实例来学习一下SQL用法:
左连接表中可能有多条数据满足情况,但只取满足条件中的第一条,即取max 自关联的查询数据
select
a.*, b.*
from
girl g,
(select *, row_number() over(ORDER BY like desc) as rn from boy) b
where b.rn = 1 and g.like = b.like
上面的意思:girl表和boy表关联,让girl找到最喜欢的那个boy;boy表是根据喜好值排序。
这样就可以用上它了。
三. 总结
row_number()的用途非常广泛,排序最好用它,它会为查询出来的每一行记录生成一个序号,依次排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。
同时,在使用排名函数的时候需要注意以下三点:
-
1、排名函数必须有 OVER 子句。
-
2、排名函数必须有包含 ORDER BY 的 OVER 子句。
-
3、分组内从1开始排序。
这个方法的多数据库兼容性也是很好的,如果你要与数据库打交道,也请收藏好它。