首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >模拟 ROW_NUMBER() 函数

模拟 ROW_NUMBER() 函数

作者头像
白日梦想家
发布2020-07-20 09:52:05
1.1K0
发布2020-07-20 09:52:05
举报
文章被收录于专栏:SQL实现SQL实现

MySQL 在 8.0 的版本推出了窗口函数,我们可以很方便地使用 row_number() 函数生成序号。

比如,对于 emp 表,我们希望根据员工入职的时间排序,入职越早排在越前面,序号从 1 开始。使用 row_number() 就可以这么写:

SELECT 
  row_number() over (
ORDER BY hiredate) AS rn,
emp.* 
FROM
  emp

排序后的结果如下图所示:

图1 入职时间升序排序

再有,如果我们希望根据部门分组,再对每个组里面的员工按照入职时间升序排序。SQL 就这么写:

SELECT 
  row_number () over (
    PARTITION BY deptno 
ORDER BY hiredate
) AS rn,
emp.* 
FROM
  emp 
ORDER BY deptno

对应的执行结果:

图2 组内按入职时间升序排序

那在 MySQL 8.0 版本之前呢,我们要怎么模拟 row_number() 函数?

方法还是比较多,接下来给大家展示一些经常用到的实现方法。

临时表 + 自增策略

如果没有分组的要求,可以创建于一个临时表,设置主键为 ,再增加一个字段,用来存储需要排序的表的主键(已根据条件排序)。原表和临时表一关联,临时表的主键就可以作为关联的结果的序号展示。

这种做法性能很好,不过只能应用于没有分组的场景。

用户变量

使用用户变量可以模拟大多数的窗口函数的功能,如果要实现上面图2 的效果,使用用户变量的写法要这样:

SELECT 
  rn,
  empno,
  ename,
  job,
  mgr,
  hiredate,
  sal,
  comm,
  deptno 
FROM
  (SELECT 
    @rn := IF(deptno = @deptno, @rn + 1, 1) AS rn,
    emp.*,
    @deptno := deptno 
  FROM
    emp,
    (SELECT 
      @deptno := NULL,
      @rn := 1) b 
  ORDER BY deptno,
    hiredate) t

使用用户变量模拟窗口函数需要注意两个地方:

  1. 排序,窗口函数里面用到分组、排序的字段,在使用用户变量的 SQL 中一定会出现在排序语句里面,而且是用于分组的字段排在前面;
  2. 赋值的表达式是 :=,比较符号用 = ,千万不能混用。

外连接

咱们又见到外连接了,外连接在这里可以这么用:

SELECT 
  COUNT(*) AS rn,a.* 
FROM
  emp a 
  LEFT JOIN emp b 
    ON b.deptno = a.deptno 
    AND a.hiredate >= b.hiredate 
GROUP BY a.empno
ORDER BY deptno,1

结果中的序号是通过 count(*) 生成,简单介绍一下生成序号的算法:

取出一列数据,遍历列里面的每个数据,统计列中每个数小于或者等于它的个数。只要比较的字段没有重复数据,生成的序号就还是连续的。

标量子查询

不喜欢用外连接,也可以通过标量子查询生成序号。

SELECT 
  (SELECT 
    COUNT(*) 
  FROM
    emp 
  WHERE deptno = a.deptno 
    AND hiredate <= a.hiredate) AS rn,
  a.* 
FROM
  emp a 
ORDER BY deptno,
  1 

要确保生成的序号无误,只需要清楚在关联的条件里一定是主表的 hiredate 字段的值大于关联表的 hiredate 的值。

写完后才发现之前已写过一篇类似的( SQL 窗口函数),想了想还是发出来,大家就当是温故知新了。

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2020-05-28,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 SQL实现 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 临时表 + 自增策略
  • 用户变量
  • 外连接
  • 标量子查询
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档