SQL奇技淫巧(01):给查出的数据排序编个号【row_number() over(order by c)】(mysql,db2,oracle,sqlserver通用)

  • 2019 年 10 月 3 日
  • 笔记

我们天天都在跟数据库打交道,写下的代码不计其数,写下的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开始排序。

这个方法的多数据库兼容性也是很好的,如果你要与数据库打交道,也请收藏好它。