PHP小码农

Appearance

表子查询（Table Subquery） ​

本节讲解表子查询（Table Subquery，也称派生表 Derived Table）在 FROM 子句中的用法、必须起别名的规则、与 CTE 的取舍、LATERAL 的配合，以及派生表无法被下推优化时的性能陷阱。学完本节，你将能把复杂查询拆成可读、可优化的分层结构。

前置知识

阅读本节前，建议先了解：行子查询（Row Subquery）

基础概念 ​

表子查询（Table Subquery） 指写在 FROM 子句中的子查询，它像一个临时表一样参与外层查询。在 SQL 标准中称为派生表（Derived Table），在 PostgreSQL 计划中表现为 Subquery Scan 节点。

概念术语说明
表子查询Table Subquery出现在 FROM 中的子查询
派生表Derived Table表子查询的标准称呼
子查询扫描Subquery Scan计划中对派生表的扫描节点
子查询提升Subquery Pull-up优化器把派生表展开到外层
扁平化Flattening提升的同义说法
别名Alias派生表必须指定，否则报错
侧向引用Lateral Reference派生表引用左侧 FROM 项的列
下推Push-down把外层条件下推到子查询内部

派生表、CTE、视图三者的关系需要先理清：

对比项派生表CTE（WITH）视图
作用域单条语句单条语句，可多次引用全局，跨语句
能否引用外层需 LATERAL否否
是否物化PG12+ 按需内联默认内联，可强制物化不物化
命名必须性必须必须建视图时命名
适合场景一次性拆分层级多处复用 / 递归长期复用逻辑

语法与用法 ​

派生表的标准语法：

text
SELECT 列列表
FROM (
  SELECT 列列表 FROM 表名 WHERE 条件 GROUP BY ...
) AS 别名                       -- 别名必须写，AS 可省略
[JOIN 其他表 ON 条件]
WHERE 外层过滤;

准备本节贯穿使用的示例表：

sql
-- 用户表
CREATE TABLE users (
  id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     TEXT    NOT NULL,
  city     TEXT    NOT NULL,
  vip      BOOLEAN NOT NULL DEFAULT false
);

-- 订单表
CREATE TABLE orders (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   TEXT           NOT NULL UNIQUE,
  user_id    INTEGER,
  amount     NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  status     TEXT           NOT NULL,
  created_at DATE           NOT NULL
);

-- 订单明细
CREATE TABLE order_items (
  id         INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  qty        INTEGER NOT NULL CHECK (qty > 0),
  price      NUMERIC(10, 2) NOT NULL
);

-- 商品表
CREATE TABLE products (
  id       INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku      TEXT    NOT NULL UNIQUE,
  name     TEXT    NOT NULL,
  category TEXT    NOT NULL
);

INSERT INTO users (name, city, vip) VALUES
  ('alice', 'beijing',  true),
  ('bob',   'shanghai', false),
  ('carol', 'shenzhen', true),
  ('dave',  'chengdu',  false);

INSERT INTO orders (order_no, user_id, amount, status, created_at) VALUES
  ('SO-1001', 1,    607.00, 'paid',    DATE '2026-01-05'),
  ('SO-1002', 2,    199.00, 'paid',    DATE '2026-01-08'),
  ('SO-1003', 1,    518.00, 'paid',    DATE '2026-02-09'),
  ('SO-1004', 3,    195.00, 'pending', DATE '2026-02-02'),
  ('SO-1005', 2,    490.00, 'paid',    DATE '2026-02-15'),
  ('SO-1006', NULL,  89.00, 'paid',    DATE '2026-03-01'),
  ('SO-1007', 3,    350.00, 'pending', DATE '2026-03-11');

INSERT INTO products (id, sku, name, category) VALUES
  (1, 'SKU-1001', '机械键盘',     'peripherals'),
  (2, 'SKU-1002', '无线鼠标',     'peripherals'),
  (3, 'SKU-1003', 'USB-C 扩展坞', 'accessories');

INSERT INTO order_items (order_id, product_id, qty, price) VALUES
  (1, 1, 1, 349.00),
  (1, 2, 2, 129.00),
  (2, 3, 1, 199.00),
  (3, 1, 1, 349.00),
  (3, 3, 1, 199.00),
  (5, 1, 1, 349.00),
  (5, 3, 1, 199.00);

ANALYZE users, orders, order_items, products;

详细说明 ​

别名规则与列重命名 ​

派生表必须指定别名，否则报语法错误：

sql
-- 错误！subquery in FROM must have an alias
SELECT * FROM (SELECT 1 AS x);
text
ERROR:  subquery in FROM must have an alias
LINE 1: SELECT * FROM (SELECT 1 AS x);
                      ^
HINT:  For example, FROM (SELECT ...) [AS] foo.
sql
-- 正确
SELECT * FROM (SELECT 1 AS x) AS t;

-- AS 可省略
SELECT * FROM (SELECT 1 AS x) t;

派生表的列可以在外层重命名，这是派生表独有的能力：

sql
-- 在派生表别名后直接列出新列名
SELECT *
FROM (SELECT id, amount, status FROM orders WHERE status = 'paid') AS paid(o_id, o_amount, o_status)
ORDER BY o_id;
text
 o_id | o_amount | o_status
------+----------+----------
    1 |   607.00 | paid
    2 |   199.00 | paid
    3 |   518.00 | paid
    5 |   490.00 | paid
    6 |    89.00 | paid
(5 rows)
sql
-- 对比：CTE 的列重命名写法不同（在 WITH 名后列出）
WITH paid(o_id, o_amount, o_status) AS (
  SELECT id, amount, status FROM orders WHERE status = 'paid'
)
SELECT * FROM paid ORDER BY o_id;

子查询提升与下推 ​

PostgreSQL 优化器会尝试把简单派生表提升（Pull-up）到外层，使得外层过滤条件能下推到子查询内部：

sql
-- 简单派生表会被提升：外层条件下推到内部
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM (SELECT id, user_id, amount, status FROM orders) AS o
WHERE o.status = 'paid';
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..13.70 rows=4 width=20) (actual time=0.011..0.017 rows=5 loops=1)
   Filter: (status = 'paid'::text)
   Rows Removed by Filter: 2
 Planning Time: 0.191 ms
 Execution Time: 0.030 ms

计划中没有 Subquery Scan 节点，说明派生表已被完全展开，WHERE 条件直接作用于基表。

阻止提升的常见因素：

因素是否阻止提升说明
含 LIMIT✅ 阻止必须在子查询内先完成截断
含 OFFSET✅ 阻止同上
含 DISTINCT✅ 阻止（部分情况）语义上不能随意下推
含聚合但外层不过滤分组列⚠️ 部分阻止见下述示例
含 UNION / GROUPING SETS✅ 阻止结构复杂
含窗口函数✅ 阻止窗口计算依赖完整输入
含集合返回函数（SRF）✅ 阻止行数在运行时才确定
sql
-- 含 LIMIT 的派生表不会被提升，出现 Subquery Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM (SELECT id, amount FROM orders ORDER BY amount DESC LIMIT 3) AS top3
WHERE top3.amount > 200;
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Subquery Scan on top3  (cost=13.92..13.96 rows=2 width=36) (actual time=0.072..0.080 rows=3 loops=1)
   Filter: (top3.amount > 200::numeric)
   ->  Limit  (cost=13.92..13.93 rows=3 width=20) (actual time=0.062..0.066 rows=3 loops=1)
         ->  Sort  (cost=13.92..13.93 rows=7 width=20) (actual time=0.058..0.060 rows=7 loops=1)
               Sort Key: orders.amount DESC
               ->  Seq Scan on orders  (cost=0.00..13.91 rows=7 width=20) (actual time=0.010..0.014 rows=7 loops=1)

Filter 出现在 Subquery Scan 之上，意味着过滤发生在子查询产出之后，无法减少内部扫描量。这是派生表最主要的性能陷阱。

sql
-- 改进：把过滤条件下推到子查询内部
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM (
  SELECT id, amount FROM orders WHERE amount > 200 ORDER BY amount DESC LIMIT 3
) AS top3;
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Limit  (cost=13.92..13.93 rows=3 width=20) (actual time=0.038..0.042 rows=3 loops=1)
   ->  Sort  (cost=13.92..13.93 rows=4 width=20) (actual time=0.036..0.038 rows=4 loops=1)
         Sort Key: orders.amount DESC
         ->  Seq Scan on orders  (cost=0.00..13.91 rows=4 width=20) (actual time=0.008..0.013 rows=4 loops=1)
               Filter: (amount > 200::numeric)          -- 条件下推成功

派生表与 LATERAL ​

普通派生表不能引用左侧 FROM 项的列；加上 LATERAL 后可以逐行求值：

sql
-- 错误！invalid reference to FROM-clause entry
SELECT u.name, t.top_amount
FROM users AS u
JOIN (SELECT amount AS top_amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) AS t
  ON true;
text
ERROR:  invalid reference to FROM-clause entry for table "u"
HINT:  There is an entry for table "u", but it cannot be referenced from this part of the query.
sql
-- 正确：显式 LATERAL
SELECT u.name, t.top_amount
FROM users AS u
LEFT JOIN LATERAL (
  SELECT amount AS top_amount
  FROM orders
  WHERE user_id = u.id
  ORDER BY amount DESC
  LIMIT 1
) AS t ON true
ORDER BY u.name;
text
 name  | top_amount
-------+------------
 alice |     607.00
 bob   |     490.00
 carol |     350.00
 dave  |
(4 rows)

LATERAL 的语义是"对左侧每一行执行一次子查询"，其执行计划表现为 Nested Loop 配合 Subquery Scan。详细用法参见 LATERAL 章节。

实战示例 ​

下面用完整流程做一份"城市维度的订单分析"报表。

sql
-- 步骤 1：基础派生表 —— 先在子查询中聚合，再在外层筛选用排名
SELECT
  t.city,
  t.order_cnt,
  t.gmv,
  t.gmv / t.order_cnt AS avg_order_value
FROM (
  SELECT u.city,
         count(DISTINCT o.id)   AS order_cnt,
         sum(o.amount)          AS gmv
  FROM users  AS u
  JOIN orders AS o ON o.user_id = u.id
  WHERE o.status = 'paid'
  GROUP BY u.city
) AS t
WHERE t.order_cnt >= 1
ORDER BY t.gmv DESC;
text
   city   | order_cnt |   gmv   | avg_order_value
----------+-----------+---------+-----------------
 beijing  |         2 | 1125.00 |          562.50
 shanghai |         2 |  689.00 |          344.50
(2 rows)
sql
-- 步骤 2：两层派生表 —— 先算订单行金额，再汇总
SELECT
  outer_t.category,
  outer_t.line_cnt,
  outer_t.amount
FROM (
  SELECT p.category,
         count(*)                AS line_cnt,
         sum(inner_t.line_amount) AS amount
  FROM (
    SELECT order_id, product_id, qty * price AS line_amount
    FROM order_items
  ) AS inner_t
  JOIN products AS p ON p.id = inner_t.product_id
  GROUP BY p.category
) AS outer_t
ORDER BY outer_t.amount DESC;
text
  category   | line_cnt | amount
-------------+----------+---------
 peripherals |        4 | 1555.00
 accessories |        3 |  597.00
(2 rows)
sql
-- 步骤 3：派生表 + 窗口函数（派生表承载窗口计算结果）
SELECT t.order_no, t.amount, t.rnk
FROM (
  SELECT o.order_no,
         o.amount,
         rank() OVER (ORDER BY o.amount DESC) AS rnk
  FROM orders AS o
  WHERE o.status = 'paid'
) AS t
WHERE t.rnk <= 3
ORDER BY t.rnk;
text
 order_no | amount  | rnk
----------+---------+-----
 SO-1001  |  607.00 |   1
 SO-1003  |  518.00 |   2
 SO-1005  |  490.00 |   3
(3 rows)
sql
-- 步骤 4：对比 CTE 写法（可读性更好，PG12+ 性能等价）
WITH ranked AS (
  SELECT o.order_no,
         o.amount,
         rank() OVER (ORDER BY o.amount DESC) AS rnk
  FROM orders AS o
  WHERE o.status = 'paid'
)
SELECT order_no, amount, rnk FROM ranked WHERE rnk <= 3 ORDER BY rnk;
sql
-- 步骤 5：观察两种写法是否为同一计划
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no, amount, rnk
FROM (SELECT order_no, amount, rank() OVER (ORDER BY amount DESC) AS rnk
      FROM orders WHERE status = 'paid') AS t
WHERE rnk <= 3;
text
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Subquery Scan on t  (cost=... ) (actual time=0.095..0.101 rows=3 loops=1)
   Filter: (t.rnk <= 3)
   ->  WindowAgg  (cost=... ) (actual time=0.062..0.095 rows=5 loops=1)
         Run Condition: (rank() OVER (?) <= 3)       -- 关键：条件下推为运行条件
         ->  Sort  (cost=... ) (actual time=... rows=5 loops=1)
               Sort Key: orders.amount DESC
               ->  Seq Scan on orders  (cost=... rows=7 width=...)
                     Filter: (status = 'paid'::text)

注意 Subquery Scan 上方的 Filter 被优化为 WindowAgg 的 Run Condition，这意味着窗口函数边算边提前停止，而不是算完全部再过滤。这是 PG 对窗口函数派生表的优化。

sql
-- 步骤 6：用派生表做"每城市 Top1 用户"
SELECT city, name, gmv
FROM (
  SELECT u.city, u.name, sum(o.amount) AS gmv,
         row_number() OVER (PARTITION BY u.city ORDER BY sum(o.amount) DESC) AS rn
  FROM users  AS u
  JOIN orders AS o ON o.user_id = u.id
  WHERE o.status = 'paid'
  GROUP BY u.city, u.id, u.name
) AS t
WHERE t.rn = 1
ORDER BY t.gmv DESC;
text
   city   | name  |   gmv
----------+-------+---------
 beijing  | alice | 1125.00
 shanghai | bob   |  689.00
(2 rows)
sql
-- 步骤 7：错误的写法 —— 条件下推被 LIMIT 阻断
-- 若在子查询里先 LIMIT 再外层过滤，会漏掉本该符合条件的数据
SELECT * FROM (SELECT id, amount FROM orders ORDER BY id LIMIT 3) AS t WHERE t.amount > 500;
text
 id | amount
----+--------
  1 | 607.00
  3 | 518.00
(2 rows)

这里 amount > 500 只在前 3 行中筛选，若业务意图是"金额大于 500 的订单取前 3 条"，则结果完全不同：

sql
-- 正确：过滤在 LIMIT 之前
SELECT * FROM (SELECT id, amount FROM orders WHERE amount > 500 ORDER BY id LIMIT 3) AS t;
text
 id | amount
----+--------
  1 | 607.00
  3 | 518.00
(2 rows)

注意事项 ​

  • 派生表必须起别名。 这是 PostgreSQL 强制要求，也是新手最常见的语法错误。
sql
-- 错误！
SELECT * FROM (SELECT 1);
-- 正确
SELECT * FROM (SELECT 1) AS t;
  • 不要把过滤条件留在派生表外面。 LIMIT、DISTINCT、窗口函数等阻止提升的结构会让外层过滤无法下推。
sql
-- 不推荐：过滤在子查询外
SELECT * FROM (SELECT id, user_id FROM orders ORDER BY id LIMIT 100) AS t WHERE t.user_id = 1;

-- 推荐：过滤条件尽量写进子查询
SELECT * FROM (SELECT id, user_id FROM orders WHERE user_id = 1 ORDER BY id LIMIT 100) AS t;
  • 不要用派生表做多次引用的"复用"。 派生表只能被引用一次，需要多处引用应改用 CTE。
sql
-- 错误！t 只能被引用一次，下面写法不合法
-- SELECT * FROM (SELECT ...) AS t WHERE a IN (SELECT a FROM t);

-- 正确：改用 CTE
WITH t AS (SELECT ...)
SELECT * FROM t WHERE a IN (SELECT a FROM t);
  • 不要嵌套过深。 三层以上嵌套会显著降低可读性，且容易阻止优化器提升。建议改用 CTE。
  • 不要在派生表内写 ORDER BY 却不配套 LIMIT。 除非外层有排序需求，内部排序通常会被丢弃，属于无效开销。
  • 不要忘记 LATERAL。 派生表引用左侧表会直接报错，必须在 JOIN 前加 LATERAL（LEFT JOIN LATERAL 或 CROSS JOIN LATERAL）。
  • 不要在派生表的重命名列表中少写列名。 列数不匹配会报错。
sql
-- 错误！column list has more columns than the subquery
SELECT * FROM (SELECT id FROM orders) AS t(a, b);
  • 不要指望派生表自带"结果缓存"。 CTE 在 PG12+ 也可能内联，需要缓存时显式写 MATERIALIZED。

最佳实践 ​

  1. 简单过滤场景直接用派生表，复杂分层场景改用 CTE。 两者在 PG12+ 中性能基本等价，CTE 更适合被多次引用。
sql
-- 派生表：只被引用一次，结构简单
SELECT t.city, t.gmv FROM (SELECT city, sum(amount) AS gmv FROM ... GROUP BY city) AS t;

-- CTE：需要多次引用或逻辑较长
WITH agg AS (SELECT city, sum(amount) AS gmv FROM ... GROUP BY city)
SELECT * FROM agg WHERE gmv > (SELECT avg(gmv) FROM agg);
  1. 始终给派生表列取有意义的别名。 特别是涉及表达式时，外层无法用原表达式引用。
sql
SELECT * FROM (
  SELECT order_id, sum(qty * price) AS line_total FROM order_items GROUP BY order_id
) AS t
WHERE t.line_total > 500;

  1. 把能下推的过滤条件写进子查询。 提升失败时这是唯一保证性能的办法。

  2. 需要逐行求值时用 LATERAL，而不是尝试用相关子查询模拟。

sql
-- 推荐
SELECT u.name, t.amount
FROM users u
LEFT JOIN LATERAL (SELECT amount FROM orders o WHERE o.user_id = u.id ORDER BY amount DESC LIMIT 1) AS t ON true;
  1. 用 EXPLAIN 检查有无 Subquery Scan。 无该节点说明已提升；有则评估过滤能否下推。
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM (SELECT id, amount FROM orders) AS t WHERE t.amount > 500;
  1. ORDER BY ... LIMIT 的"Top-N"查询把过滤条件放在 LIMIT 之前。
sql
-- 正确顺序：过滤 → 排序 → 截断
SELECT * FROM (SELECT id, amount FROM orders WHERE amount > 500 ORDER BY amount DESC LIMIT 10) AS t;
  1. 多层嵌套时给每一层显式命名，或用 CTE 拆开。 inner_t、outer_t 这类命名有助于阅读，但更推荐用语义化名称。

下一节

继续学习：EXISTS 子查询

参考链接 ​