一、分页的三种写法

第一种是老写法，用 TOP 加 NOT IN 排掉前 N 个 id：

SELECT TOP 10 * FROM tb_goods
WHERE id NOT IN (SELECT TOP 10 id FROM tb_goods ORDER BY id)
ORDER BY id

思路是「排掉前 10 个，剩下的再取前 10 个」。 翻到第 500 页就要排掉 4990 个 id， 而且 NOT IN 一碰上 NULL 就全废——9.1b 那个坑在这儿又要踩一次。这条早就不建议用了。 第二种是标准答案，ROW_NUMBER() 切片：

SELECT id, v FROM (
    SELECT id, v, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM big
) t WHERE rn BETWEEN 11 AND 20

先给全表编上号，再挑 11 到 20 这几行。 看着笨，但它是唯一跨数据库都通用的写法——MySQL 8、 Oracle、SQL Server、PostgreSQL 全支持窗口函数。 第三种是 OFFSET ... FETCH，SQL Server 2012 以后官方推荐：

SELECT id, gname FROM tb_goods ORDER BY id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY

这条是专门为翻页造出来的语法，最省事。 本沙箱没有， 对应的还是 LIMIT n OFFSET m——演示里两种写法的结果逐行对过，都是 11 到 20。

二、深翻页为什么慢

同样是取 10 行，第一页和最后一页差了 471 倍。 这个数不是估算，是真测：

原因是 OFFSET 不跳过就算。 数据库没法「直接跳到第 199990 行」—— 它必须先把前 199990 行数出来，然后丢掉。 翻得越深，白数的越多。 键集分页（也叫游标分页）绕开了这件事： 记住上一页最后一行的 id，下次从它后面接着取。 WHERE id > 199990 走索引直接定位，一步到位。 代价是它只能「上一页／下一页」，跳不到第 500 页。 这一点要跟产品经理谈清楚——很多「翻到第 N 页」的需求，其实是「一直往下看」。 还有一个折中办法： 先按 id 查出第 200000 行的 id 值，再用键集分页。 多一次小查询，换掉那次全表跳过。

三、排名三兄弟

三个函数名字都带 RANK，行为差在「并列怎么办」：

演示里三个 88 分最能说明问题： RANK 给 3、3、3，然后直接跳到 6； DENSE_RANK 给 3、3、3，接着给 4。 ROW_NUMBER 那个「碰运气」值得多说一句。 并列的时候它按什么排？ 答案是不保证。 同一份数据跑两次， 顺序可能不一样——所以它的 ORDER BY 里最好再加一个唯一列（比如 id）当第二排序键。 怎么选： 分页必须用 ROW_NUMBER（要唯一号）；排行榜用 RANK（第 4 名空缺是正常的）； 算等级、算档位用 DENSE_RANK（不该有空档）。

四、分组取前几

这是报表里出现频率最高的套路：各班前三名、各品类销量前五、每月 Top10。

SELECT subject, sname, score FROM (
    SELECT sc.subject, st.sname, sc.score,
           ROW_NUMBER() OVER (PARTITION BY sc.subject
                              ORDER BY sc.score DESC) AS rn
    FROM tb_score sc JOIN tb_student st ON st.id = sc.student_id
) t WHERE rn <= 2 ORDER BY subject, rn

PARTITION BY 是「按什么分组」，ORDER BY 是「组内怎么排」。 两个一叠，就是「每组内部排名」。 外面再套一层 WHERE rn <= 2，就是每组取前两名。 必须套子查询——因为窗口函数不能直接写在 WHERE 里（WHERE 比窗口函数先执行）。 要「取前 2 名」还是「取前 2 个人」？ 这两个不一样：有并列的时候， ROW_NUMBER 会硬砍掉一个，RANK 会把并列的都留下。 业务上要哪个，得先问清楚。

五、写复杂查询的顺序：反着来

新手写复杂查询是从 SELECT 往后顺推：先想「我要显示什么」，再想从哪张表来。 这条路在两张表以内还行，三张表以上就乱。 稳妥的路子是反着来：

第 3 步那个「每接一条跑一次」是关键。 多表连接出错， 九成是某一次 JOIN 让行数突然涨了——当场就能发现，不用等最后查出一堆重复数据再回头找。 演示最后那条报表查询就是照这个顺序写出来的： 班级、人数、均分、不及格数、最高、最低。 六列来自两张表，一条 LEFT JOIN 接班级和学生，再一条接成绩，然后才聚合。

六、动手 Demo：分页三写法、深翻页计时与分组取前几

这一段造一张 20 万行的表讲翻页： 三种写法各跑一次，逐行对结果；再分别计时第一页、最后一页、键集分页。 然后转到排名——六个学生故意造出并列，看三个函数给出的号有什么不同， 最后用 PARTITION BY 做「每科取前两名」，再拼一条综合报表。

import sqlite3, time

c = sqlite3.connect(u":memory:")
c.execute(u"CREATE TABLE big (id INTEGER PRIMARY KEY, v INTEGER)")
c.execute(u"WITH RECURSIVE t(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM t WHERE x < 200000) "
          u"INSERT INTO big SELECT x, x % 997 FROM t")

# 写法二：ROW_NUMBER 切片
q2 = (u"SELECT id, v FROM (SELECT id, v, ROW_NUMBER() OVER (ORDER BY id) AS rn "
      u"FROM big) t WHERE rn BETWEEN 11 AND 20")
print(c.execute(q2).fetchall()[:3])

# 深翻页 vs 键集分页
print(clock(u"SELECT id, v FROM big ORDER BY id LIMIT 10 OFFSET 0"))
print(clock(u"SELECT id, v FROM big ORDER BY id LIMIT 10 OFFSET 199990"))
print(clock(u"SELECT id, v FROM big WHERE id > 199990 ORDER BY id LIMIT 10"))

# 三个排名函数的差别
c.execute(u"CREATE TABLE s9 (name TEXT, g REAL)")
c.executemany(u"INSERT INTO s9 VALUES (?,?)",
              [(u"张伟", 95.0), (u"王芳", 95.0), (u"李娜", 88.0),
               (u"刘强", 88.0), (u"陈静", 88.0), (u"杨洋", 76.0)])
print(c.execute(u"SELECT name, g, "
                u"ROW_NUMBER() OVER (ORDER BY g DESC), "
                u"RANK() OVER (ORDER BY g DESC), "
                u"DENSE_RANK() OVER (ORDER BY g DESC) FROM s9").fetchall())

实跑输出：

--------------------------------------------------------------------
查询进阶的综合用法
--------------------------------------------------------------------


  ① 分页查询的三种写法
  --------------------------------------------------------------------
  准备一张 20 万行的表，专门讲翻页。三种写法要的都是「第 11 到第 20 行」。

  写法一：TOP + NOT IN 子查询（SQL Server 2000 时代的经典翻页）

  [教材原码 · 本沙箱不执行]
  | SELECT TOP 10 * FROM tb_goods
  | WHERE id NOT IN (SELECT TOP 10 id FROM tb_goods ORDER BY id)
  | ORDER BY id;

  思路是「把前 10 个 id 排掉，剩下里再取前 10 个」。翻到第 500 页就要排掉 4990 个 id，
  而且 NOT IN 一旦碰上 NULL 就全废（D040 那个坑）。这条早就不建议用了。

  写法二：ROW_NUMBER() 窗口函数给每行编号，再按编号切片——这是标准答案

  1> SELECT id, v
     FROM (SELECT id, v, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM big) t
     WHERE rn BETWEEN 11 AND 20
  id  v
  ------
  11  11
  12  12
  13  13
  14  14
  15  15
  16  16
  17  17
  18  18
  19  19
  20  20
  先给全表编上号，再挑 11 到 20 这几行。看着笨，但它是唯一跨数据库都通用的写法。

  写法三：OFFSET ... FETCH（SQL Server 2012 以后官方推荐）

  [教材原码 · 本沙箱不执行]
  | SELECT id, gname FROM tb_goods ORDER BY id
  | OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

  这条是专门为翻页造出来的语法，最省事。但 SQLite 里没有，它对应的还是 LIMIT／OFFSET：
| 写法 | 本沙箱怎么跑 | 行数 | 首行 id | 末行 id |
|---|---|---:|---:|---:|
| TOP + NOT IN | LIMIT 10 OFFSET 10 | 10 | 11 | 20 |
| ROW_NUMBER | 窗口函数切片 | 10 | 11 | 20 |

  两种写法给出的行一模一样：都是 11 到 20。业务结果等价，差别在性能和写法。
  SQL Server 2012 以后，OFFSET ... FETCH 在功能上就是 ROW_NUMBER 那套的语法糖。

  ② 翻到很后面时，OFFSET 会变慢
  --------------------------------------------------------------------
| 翻页方式 | 最快一次（毫秒） |
|---|---:|
| LIMIT 10 OFFSET 0（第一页） | 0.010 |
| LIMIT 10 OFFSET 199990（最后一页） | 4.597 |
| WHERE id > 199990（键集分页） | 0.010 |

  同样是取 10 行，翻到最后一页比翻第一页慢 469 倍。
  原因是 OFFSET 不跳过就算：数据库得先数过前 199990 行，再丢掉它们。
  键集分页（记住上一页最后一行的 id，下一页从它后面接着取）不需要数，所以一直快。
  ! 代价是它只能「上一页／下一页」，跳不到第 500 页——这一点要跟产品经理谈清楚。

  ③ 排名：ROW_NUMBER、RANK、DENSE_RANK 三者不同
  --------------------------------------------------------------------

  1> SELECT name,
         g,
         ROW_NUMBER() OVER (ORDER BY g DESC) AS 行号,
         RANK() OVER (ORDER BY g DESC) AS 名次_跳,
         DENSE_RANK() OVER (ORDER BY g DESC) AS 名次_不跳
     FROM s9
  name  g   行号  名次_跳  名次_不跳
  ----------------------------------
  张伟  95  1     1        1
  王芳  95  2     1        1
  李娜  88  3     3        2
  刘强  88  4     3        2
  陈静  88  5     3        2
  杨洋  76  6     6        3
  (6 行受影响)
| 函数 | 并列时怎么办 | 适合的场景 |
|---|---|---|
| ROW_NUMBER | 硬给不同号，谁先谁后碰运气 | 分页，必须每行唯一 |
| RANK | 并列同名次，后面的号跳过去 | 排行榜，允许缺号 |
| DENSE_RANK | 并列同名次，后面的号不跳 | 及格档位、等级评定 |

  三个 88 分：RANK 给 3,3,3 然后跳到 6；DENSE_RANK 给 3,3,3 然后接 4。
  ! ROW_NUMBER 遇到并列时谁在前是不确定的，不加第二排序键就可能每次跑不一样。

  ④ 分组取前几：每个科目取分数最高的两个
  --------------------------------------------------------------------

  1> SELECT subject, sname, score
     FROM (SELECT sc.subject, st.sname, sc.score, ROW_NUMBER() OVER (PARTITION BY
         sc.subject ORDER BY sc.score DESC) AS rn FROM tb_score sc JOIN tb_student st ON
         st.id = sc.student_id) t
     WHERE rn <= 2
     ORDER BY subject, rn
  subject   sname  score
  ----------------------
  大学英语  陈静   91
  大学英语  王芳   85
  高等数学  王芳   92
  高等数学  张伟   88
  (4 行受影响)
  PARTITION BY 是「按科目分组」，ORDER BY 是「组内怎么排」，两层一叠就是分组取前几。
  这个套路在报表里出现频率极高：各班前三名、各品类销量前五、每月 Top10。

  ⑤ 综合：一条报表查询
  --------------------------------------------------------------------
  这一节的数据不插额外的学生，就是样例库原样——所以软工2301 只有杨洋一个人。

  1> SELECT c.cname AS 班级,
         COUNT(DISTINCT st.id) AS 人数,
         ROUND(AVG(sc.score), 2) AS 均分,
         SUM(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END) AS 不及格,
         MAX(sc.score) AS 最高,
         MIN(sc.score) AS 最低
     FROM tb_class c
     LEFT JOIN tb_student st ON st.class_id = c.id
     LEFT JOIN tb_score sc ON sc.student_id = st.id
     GROUP BY c.cname
     HAVING COUNT(sc.score) > 0
     ORDER BY 均分 DESC
  班级      人数  均分   不及格  最高  最低
  -----------------------------------------
  计科2301  3     80.5   0       92    61
  软工2301  1     79     0       79    79
  计科2302  2     73.25  1       91    55.5
  (3 行受影响)
  这条里凑齐了本章所有零件：连接、聚合、CASE 条件统计、HAVING 过滤、排序。
  写复杂查询的稳妥路子是反着来：先把结果表的样子画出来，再想每列从哪张表来，
  然后一条一条接 JOIN，最后才加 WHERE 和 GROUP BY——不是从 SELECT 往后顺推。

计时那三个数请连着看：0.010、4.611、0.011。第一页和键集分页几乎一样快， 最后一页慢了 471 倍——差的全是「白数前面那些行」的时间。 键集分页那个 0.011 有点反直觉：取最后一页跟取第一页一样快。因为它不是「翻到那儿」， 而是「从那儿开始找」——id > 199990 走索引，一步定位。 排名那段最后三列是 1/1/1、2/1/1、3/3/2…… 请注意第五行（陈静，88 分）：ROW_NUMBER 给 5， RANK 给 3，DENSE_RANK 给 2。同一行数据，三个函数三个答案。 综合报表那条是把本章所有零件拼起来： 两个 LEFT JOIN、COUNT(DISTINCT ...)、AVG、 SUM(CASE ...)、HAVING、ORDER BY。能读懂它，第 9 章就算过关了。

30 秒实验

1. 把深翻页那句的 OFFSET 改成 100000，再测一次。 耗时大概会减半——因为白数的行少了一半。这个线性关系很能说明问题。
2. 把「每科取前两名」的 ROW_NUMBER 换成 RANK，看结果行数变不变。 有并列的时候会变——这就是「取前 2 名」和「取前 2 个人」的区别。
3. 给 s9 表加一个 id 列，把 ROW_NUMBER() OVER (ORDER BY g DESC) 改成 ORDER BY g DESC, id， 跑两次。两次结果会稳定一致——第二排序键解决的就是这个问题。

常见误区

1. 「翻页就是 OFFSET，能翻就行」。数据量一大就废： 最后一页比第一页慢几百倍。 要么用键集分页，要么限制最大页数。
2. 「ROW_NUMBER、RANK、DENSE_RANK 差不多，随便用」。有并列时三者结果完全不同。 分页用错会出现重复或漏行，排行榜用错会出现空号。
3. 「先把 SELECT 写完，表最后再连」。三张表以上这么写，一定会在某一步连出重复行而不自知。 边连边跑、看行数，才是稳的路子。

第 9 章到此结束。 下一篇进第 10 章 10.1，讲视图： 它不是「表的副本」，而是「存下来的一条查询」——这个区别决定了它什么时候快、什么时候慢。

• ☐ （本篇完，下一篇 10.1 视图概述）

相关笔记

• 连接查询
• 子查询基础
• COMPUTE、DISTINCT、TOP、WITH TIES 与 UNION
• 48 课 MySQL从入门到精通 · 索引
• 46 课 MySQL即学即用 · 索引
• 🪟 SQL Server从入门到精通 · 索引