高级 SQL

是什么，为什么存在

“高级 SQL”不是标准中的独立语言层级，而是一组处理多步关系问题的查询能力。 窗口函数（window function） 在保留明细行的同时计算排名、偏移和累计值；公共表表达式把长查询拆成命名关系；递归查询反复扩展一组行；存在性谓词则表达“至少有一个”或“一个也没有”。这些能力让原本需要应用代码循环处理的数据，继续留在一个声明式查询中。

普通聚合会把一组输入行折叠成一行，窗口函数不会。CTE 也不是临时表的同义词，它首先是当前语句中的命名查询。递归 CTE 更不是任意循环：每轮都必须从上一轮结果推导新行，并能证明过程会停止。

你会在按组取前几名、累计指标、相邻事件比较、组织树、类别路径、缺失关系和排除名单中遇到这些结构。它们的共同难点不是关键字记忆，而是精确定义行集、顺序、边界和 NULL 语义。

本文以本地 Python 运行时附带的 SQLite 3.45.1 为可执行方言。核心关系思想也适用于 PostgreSQL 等数据库，但日期函数、递归限制、物化策略和部分窗口语法会因产品而异，上线前应对目标数据库重新运行测试。

工作原理

一个 SELECT 可以看成逐步形成结果关系的逻辑管线。数据库不必按这个顺序执行物理操作，但名称可见性和表达式合法位置受逻辑阶段约束；这就是窗口结果不能直接出现在同层 WHERE 中的原因。

优化器可以把过滤下推、改写连接或选择不同访问路径，只要结果仍符合 SQL 语义。因此，逻辑顺序用于判断正确性，执行计划用于解释实际工作，两者不能混为一谈。

分区、顺序与框架

窗口函数的 OVER 子句有三个彼此独立的维度：

1. PARTITION BY 把输入分成互不影响的分区；省略时，全部输入行属于一个分区。
2. 窗口内的 ORDER BY 定义排名、偏移或累计计算所看到的逻辑顺序；它不保证最终输出顺序。
3. 窗口框架（window frame） 从当前分区中选择当前行参与计算的那一段；ROWS、GROUPS 和 RANGE 的边界含义不同。

ROW_NUMBER()、RANK() 和 DENSE_RANK() 都依赖窗口顺序，但处理并列值的方式不同。ROW_NUMBER() 总会编号为不同位置；RANK() 为并列行给出相同名次并留下空位；DENSE_RANK() 不留空位。只有排序键唯一时，ROW_NUMBER() 才能稳定选择同一行。

命名中间关系

公共表表达式（common table expression，CTE） 由 WITH name AS (...) 定义，并只在随后的一条语句中可见。它适合给查询阶段命名，例如先筛选合格订单，再编号，最后选择每组前两条。名称改善了推理边界，却不自动保存结果或提升性能。

CTE 的列仍然构成关系。外层查询只能引用 CTE 实际投影出的列，而同层过滤不能引用尚未计算的窗口结果。需要按窗口值过滤时，先在 CTE 中计算，再由外层 WHERE 读取别名。

递归扩展行集

递归 CTE（recursive CTE） 包含锚成员和递归成员。锚成员产生起始行；递归成员引用 CTE 的上一轮输出，并产生下一轮行。两部分通过 UNION ALL 或 UNION 组成一个复合查询，直到某一轮不再产生新行。

可靠的递归查询需要业务终点和防御边界。树结构通常以“没有子节点”为自然终点，但脏数据可能形成环，所以查询还应记录已经访问的标识；深度上限用于限制损害，不能代替环检测。租户或权限条件也必须在锚成员和递归成员中持续成立。

存在性与反连接

EXISTS (subquery) 只关心子查询是否至少返回一行，选出的具体列和值都不重要。相关子查询可以引用外层当前行，因此 NOT EXISTS 能直接表达“没有与此行匹配的记录”，也就是 反连接（anti-join） 。

NOT IN 看似表达同一意思，却受三值逻辑影响。只要右侧集合含有 NULL，一个不匹配的值与整个列表比较后也可能得到 UNKNOWN，而 WHERE 只保留 TRUE。排除可空列时，NOT EXISTS 的意图通常更清楚。

示例

下面四个示例都可以运行，并逐步覆盖窗口、分层查询、递归和空值安全的排除逻辑。下方可见的 shop 固定数据为窗口查询、最高订单查询和反连接查询提供共享数据；递归示例则在代码中声明自己的类别树。输出来自 SQLite 3.45.1，而不是手工推算。

CREATE TABLE sales(rep TEXT, sold_on TEXT, amount INTEGER);
INSERT INTO sales VALUES
  ('Ari', '2026-08-01', 120),
  ('Ari', '2026-08-02', 180),
  ('Ari', '2026-08-03', 180),
  ('Bo',  '2026-08-01', 90),
  ('Bo',  '2026-08-02', 140),
  ('Bo',  '2026-08-03', 110);

CREATE TABLE orders(
  order_id INTEGER PRIMARY KEY,
  customer TEXT,
  ordered_on TEXT,
  amount INTEGER
);
INSERT INTO orders VALUES
  (1, 'Acme', '2026-08-01', 90),
  (2, 'Acme', '2026-08-03', 150),
  (3, 'Acme', '2026-08-04', 120),
  (4, 'Nova', '2026-08-01', 200),
  (5, 'Nova', '2026-08-02', 80),
  (6, 'Nova', '2026-08-05', 140);

CREATE TABLE accounts(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE suspensions(account_id INTEGER);
INSERT INTO accounts VALUES
  (1, 'Ari'),
  (2, 'Bo'),
  (3, 'Cy');
INSERT INTO suspensions VALUES (2), (NULL);

同时计算编号、并列名次与累计值

第一个查询对每位销售代表分别计算三种结果。编号使用日期作为第二排序键，所以相同金额下仍有确定顺序；累计金额则按销售日期使用显式的 ROWS 框架。

Ari 的两笔 180 共享 amount_rank = 1，但 row_no 分别为 1 和 2。最终结果按日期展示，不会改变窗口函数先前按金额计算出的编号。

running_amount 的顺序与排名不同，因为每个窗口函数可以拥有自己的 OVER 子句。把多个分析值放在一个查询中，并不意味着它们必须共享分区、顺序或框架。

用 CTE 过滤窗口结果

下一个查询要取每位客户金额最高的两笔订单。position 在 ranked_orders 中计算，外层查询才能合法地在 WHERE 中使用它。

WITH ranked_orders AS (
  SELECT
    order_id,
    customer,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer
      ORDER BY amount DESC, order_id
    ) AS position
  FROM orders
  WHERE ordered_on >= '2026-08-01'
)
SELECT customer, order_id, amount
FROM ranked_orders
WHERE position <= 2
ORDER BY customer, position;

customer | order_id | amount
---------+----------+-------
Acme     | 2        | 150
Acme     | 3        | 120
Nova     | 4        | 200
Nova     | 6        | 140

order_id 是稳定的最终排序键。即使两笔订单金额相同，查询仍能确定哪一笔占据较小的 position，测试也不会依赖数据库碰巧采用的扫描顺序。

如果需求是“包含并列的前两种金额”，这里应改用 DENSE_RANK()。选择排名函数前必须先定义业务中的“前两名”指行数、竞赛名次，还是不同数值层级。

遍历带防环条件的层次结构

递归示例从根类别开始向下遍历。路径使用带分隔符的标识序列，避免把标识 1 误判为已经包含在 11 中；深度上限为异常数据提供第二道边界。

CREATE TABLE categories(
  id INTEGER PRIMARY KEY,
  parent_id INTEGER,
  name TEXT
);
INSERT INTO categories VALUES
  (1, NULL, 'Store'),
  (2, 1, 'Data'),
  (3, 1, 'Tools'),
  (4, 2, 'SQL'),
  (5, 2, 'Python'),
  (6, 4, 'Window functions');

WITH RECURSIVE category_tree(id, name, depth, path) AS (
  SELECT id, name, 0, printf('/%d/', id)
  FROM categories
  WHERE id = 1

  UNION ALL

  SELECT c.id, c.name, t.depth + 1, t.path || c.id || '/'
  FROM categories AS c
  JOIN category_tree AS t ON c.parent_id = t.id
  WHERE t.depth < 10
    AND instr(t.path, printf('/%d/', c.id)) = 0
)
SELECT depth, name
FROM category_tree
ORDER BY depth, id;

depth | name
------+-----------------
0     | Store
1     | Data
1     | Tools
2     | SQL
2     | Python
3     | Window functions

锚成员只选择 id = 1。递归成员每轮连接当前层的子类别，并把新标识追加到路径；如果新标识已经在路径中，该分支不再扩展。

这里的路径函数和字符串拼接属于 SQLite 方言。生产系统还应通过外键、唯一约束或写入验证阻止环进入数据，而不是只依靠每个读取查询自行防御。

用 NOT EXISTS 排除可空关系

最后一个示例故意在停用列表中放入 NULL，并把 NOT IN 与 NOT EXISTS 的结果合并展示。NOT IN 分支没有返回行，只有反连接分支保留未停用账户。

WITH methods(method, account_id, account_name) AS (
  SELECT 'NOT IN', a.id, a.name
  FROM accounts AS a
  WHERE a.id NOT IN (
    SELECT account_id FROM suspensions
  )

  UNION ALL

  SELECT 'NOT EXISTS', a.id, a.name
  FROM accounts AS a
  WHERE NOT EXISTS (
    SELECT 1
    FROM suspensions AS s
    WHERE s.account_id = a.id
  )
)
SELECT method, account_id, account_name
FROM methods
ORDER BY method, account_id;

method     | account_id | account_name
-----------+------------+-------------
NOT EXISTS | 1          | Ari
NOT EXISTS | 3          | Cy

对于账户 1，1 <> 2 为真，但 1 <> NULL 是 UNKNOWN；整个 NOT IN 条件不能成为 TRUE。NOT EXISTS 逐行寻找等值匹配，NULL = 1 不会形成匹配，所以 Ari 和 Cy 被正确保留。

如果业务把 NULL 视为另一个明确类别，应先把这项规则写进数据模型或过滤条件。不要依靠某个查询作者记得右侧列当前“应该没有空值”。

陷阱

修复方法： 在窗口 ORDER BY 末尾加入唯一且有业务意义的稳定键，例如 created_at DESC, order_id DESC。如果需求要求保留并列，使用 RANK() 或 DENSE_RANK()，不要用任意键偷偷打破并列。

修复方法： 累计或移动计算应显式写出 ROWS BETWEEN ...、GROUPS BETWEEN ... 或目标方言支持的 RANGE 边界。用重复排序值测试当前行的结果，而不只测试所有值都唯一的数据。

修复方法： 在子查询或 CTE 中计算窗口值，再由外层查询过滤。支持 QUALIFY 的数据库可以使用该子句，但 SQLite 3.45.1 不支持，跨数据库代码不能假定它存在。

修复方法： 维护带可靠分隔符的已访问路径，发现重复节点时停止或单独报告。把深度上限保留为资源保护，并在数据写入处执行无环约束或验证。

修复方法： 对相关排除条件使用 NOT EXISTS。如果必须使用 NOT IN，应在子查询中明确排除 NULL，并为右侧包含空值的情况添加回归测试。

修复方法： 先按关系语义写出正确查询，再用目标数据库的执行计划检查实际行为。只有测量证明确有问题时，才使用该产品提供的物化提示、临时表或索引策略。

延伸阅读

• SQLite 窗口函数
• SQLite WITH 子句
• SQLite SELECT 文档
• PostgreSQL 窗口函数教程