一、三个函数的分岔口：并列

over (order by 列) 的意思是「按这个排序，算一个序号」，这一串叫窗口（window）。 row_number() 每行一个号，绝不留空：1、2、3、4、5… 就算三行并列，也是 2、3、4。 rank() 并列同名、后面跳号：1、2、2、2、5。 dense_rank() 并列同名、后面不跳号：1、2、2、2、3。 三个一起记：row_number 抢座、rank 跳号、dense_rank 不跳。

二、什么时候用哪个

要「前 N 名」且不许并列挤掉名额——用 row_number（每行一个号，好截断）。 要「跟体育比赛一样，并列第 2、下一个第 5」——用 rank。 要「并列第 2、下一个第 3」（比如分等级）——用 dense_rank。 实战里最常出错的是这个：rank() <= 3 想取前三名，结果因为并列返回了 5 行。 要严格三行就换 row_number。

三、分组内排名：partition by

over (partition by class_id order by height desc) ——按班级分成几个窗口，各排各的， 所以每个班都有自己的第 1 名。 没有 partition by 就是「整张表一个窗口」， 加了就是「每组一个窗口」。 这一点跟 group by 的思路像， 但结果完全相反：group by 把行压掉，窗口函数保留每一行。 这就是窗口函数的价值：它既保留了明细，又给出了汇总视角。 以前要两件事都做， 得写两次查询再拼。

四、取每组第一名：必须套一层子查询

排名不能直接写进 where——where 在窗口函数之前执行，那时还没有 rk 这个值。 写 WHERE rk = 1 会报「没有这一列」。 办法是套一层：内层算出 rk，外层 WHERE t.rk = 1。 这个「套一层再筛」是窗口函数的标准套路。 为什么不能省：窗口函数的结果是「算完之后才有的列」， where 看不到它，having 也看不到（它只认聚合结果）。 只有 order by 和再外一层的查询能看到。

五、5.7 没有这套东西，替代写法是什么

旧写法一：自连接 + group by 先算出每组最大值，再连回去。 写法二：用用户变量一行行累加（@rn := @rn + 1），慢、难读、还依赖执行顺序。 8.0 之后这两个都可以扔了。 但如果你维护的是 5.7 的库，看到这两种写法别惊讶， 那是当年的标准做法。 本沙箱（SQLite 3.53）支持窗口函数，所以这一篇的 Demo 是真跑的。 这一点比很多旧版本 MySQL 还强。

六、动手 Demo：三个 92 分，三种排法

Demo 四段：先看数学成绩的并列在哪，再三个函数一起排，然后分组内排名，最后取每组第一名。 第二段是核心：同一份数据，同一列分数，三列序号给出三种答案。

import _q46 as Q
c = Q.con()

# 先看并列在哪
c.execute("SELECT s.name, sc.score FROM scores sc "
          "JOIN students s ON s.id = sc.student_id "
          "WHERE sc.course = '数学' ORDER BY sc.score DESC")
# 三个排名函数一起对比
c.execute("SELECT s.name, sc.score, "
          "RANK() OVER (ORDER BY sc.score DESC) AS rk, "
          "ROW_NUMBER() OVER (ORDER BY sc.score DESC) AS rn, "
          "DENSE_RANK() OVER (ORDER BY sc.score DESC) AS dr "
          "FROM scores sc JOIN students s ON s.id = sc.student_id "
          "WHERE sc.course = '数学' ORDER BY sc.score DESC LIMIT 8")
# 分组内排名
c.execute("SELECT s.name, s.class_id, s.height, "
          "RANK() OVER (PARTITION BY s.class_id "
          "ORDER BY s.height DESC) AS rk "
          "FROM students s ORDER BY s.class_id, rk")
# 取每组第一名：套一层子查询
c.execute("SELECT t.name, t.class_id, t.height FROM ("
          "SELECT s.name, s.class_id, s.height, "
          "RANK() OVER (PARTITION BY s.class_id "
          "ORDER BY s.height DESC) AS rk FROM students s) AS t "
          "WHERE t.rk = 1")

实跑输出：

--------------------------------------------------------------------
第二十七课：三个排名函数，差别只在「并列怎么办」
--------------------------------------------------------------------

  先把数学成绩排出来，看并列在哪
  SQL> SELECT s.name, sc.score FROM scores sc JOIN students s ON s.id = sc.student_id
       WHERE sc.course = '数学' ORDER BY sc.score DESC
  name     score
  ---------------
  张伟     95.00
  王芳     92.00
  李娜     92.00
  刘洋     92.00
  陈静     88.00
  杨帆     85.00
  赵敏     85.00
  黄磊     78.00
  周涛     72.00
  吴倩     68.00
  徐明     61.00
  孙悦     55.00
  （12 行）

  **12 个人里有两组并列**：92 分三个人（王芳、李娜、刘洋），85 分两个人（杨帆、赵敏）。
  **「并列怎么排」正是三个函数的分岔口。**

  三个函数一次排出来对比
  SQL> SELECT s.name, sc.score, RANK() OVER (ORDER BY sc.score DESC) AS rk,
       ROW_NUMBER() OVER (ORDER BY sc.score DESC) AS rn,
       DENSE_RANK() OVER (ORDER BY sc.score DESC) AS dr FROM scores sc JOIN students s
       ON s.id = sc.student_id WHERE sc.course = '数学' ORDER BY sc.score DESC LIMIT 8
  name     score     rk     rn     dr
  ---------------------------------------
  张伟     95.00     1      1      1
  王芳     92.00     2      2      2
  李娜     92.00     2      3      2
  刘洋     92.00     2      4      2
  陈静     88.00     5      5      3
  杨帆     85.00     6      6      4
  赵敏     85.00     6      7      4
  黄磊     78.00     8      8      5
  （8 行）

  **三个 92 分：`rank` 都是 2、`row_number` 是 2／3／4、`dense_rank` 都是 2。**
  **下一个 88 分：`rank` 给 5（跳过了 3、4），`dense_rank` 给 3（不跳）。**
  **一句话记**：`row_number` 一定连续，`rank` 并列后跳号，`dense_rank` 并列后不跳号。
  **`over (order by …)` 这一串叫「窗口」**，意思是「在这个排序范围里算」。
  **它是 MySQL 8 才有的，5.7 及以前没有排名函数。**

  分组内排名：加一个 partition by
  SQL> SELECT s.name, s.class_id, s.height,
       RANK() OVER (PARTITION BY s.class_id ORDER BY s.height DESC) AS rk FROM students s
       ORDER BY s.class_id, rk
  name     class_id     height     rk
  ------------------------------------------
  杨帆     1            175.10     1
  周涛     1            174.00     2
  张伟     1            172.50     3
  王芳     1            160.20     4
  刘洋     2            178.30     1
  李娜     2            165.00     2
  吴倩     2            163.50     3
  赵敏     2            162.80     4
  黄磊     3            180.20     1
  徐明     3            169.70     2
  孙悦     3            161.40     3
  陈静     3            158.60     4
  （12 行）

  **`partition by class_id` 的意思是「按班级分成几个窗口，各排各的」**，
  **所以每个班都有自己的第 1 名。**
  **这就是「每个班最高的那个」这类题的解法**——**比 `group by` 再自连接简单得多。**

  取每组第一名：套一层子查询
  SQL> SELECT t.name, t.class_id, t.height FROM (SELECT s.name, s.class_id, s.height,
       RANK() OVER (PARTITION BY s.class_id ORDER BY s.height DESC) AS rk FROM students s) AS t
       WHERE t.rk = 1
  name     class_id     height
  -------------------------------
  杨帆     1            175.10
  刘洋     2            178.30
  黄磊     3            180.20
  （3 行）

  **排名不能直接写进 `where`**（`where` 在窗口函数之前跑），
  **所以套一层子查询，在外面按 `rk = 1` 筛。**
  **这个「套一层再筛」的套路，是窗口函数的标准用法**，**记住它，比记住函数名有用。**

第一段原始成绩。 12 个人，92 分三个（王芳、李娜、刘洋），85 分两个（杨帆、赵敏）。 第二段四列并排。 三个 92 分：rk 都是 2、rn 是 2／3／4、dr 都是 2。 下一个 88 分：rk 跳成 5，dr 给 3。 第三段分组内排名。 每个班自己的 1、2、3、4——三个班三个第 1 名。 第四段取每组第一。 杨帆 175.10、刘洋 178.30、黄磊 180.20，三个班各自最高的那个。

30 秒实验

1. 把 RANK() 换成 ROW_NUMBER()，第二段的 rk 列会变成什么样？
2. PARTITION BY class_id 去掉之后，第三段的 rk 还是每班 1 到 4 吗？
3. 想取「每个班前两名」，WHERE t.rk <= 2 与 WHERE t.rn <= 2 结果一样吗？

常见误区

1. 用 rank() <= 3 取前三名。 有并列时会多返回几行，该用 row_number()。
2. 把排名写进 where。 窗口函数在 where 之后才算，那里看不到别名，必须套一层子查询。
3. 以为窗口函数会像 group by 那样把行压掉。 它保留每一行，只是在每行后面附一个汇总视角的值。

排名讲完，下一篇换个话题：md5 与 sha1，以及为什么它们不该拿来存密码。

• ☐ （本篇完，下一篇 6.6 加密函数）

相关笔记

• 时间函数
• 分组子句：group by
• 🦏 MySQL即学即用 · 索引