高级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窗口函数教程