SQL 模拟 ROW_NUMBER() 函数

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 窗口函数），想了想还是发出来，大家就当是温故知新了。