PostgreSQL / 系统课时

使用标量与集合子查询

使用标量与集合子查询

本课解决“一个查询如何使用另一个查询的结果”的问题。你将区分只返回一个值的标量子查询与返回多行的集合子查询,并掌握它们在 SELECT、WHERE 和 FROM 中的典型写法。

学习目标

  • 区分标量子查询与集合子查询
  • 在三个常见位置中正确使用子查询
  • 识别返回行数不符合预期的错误

两类子查询

标量子查询必须返回一行一列,适合放在表达式位置,例如计算平均值后与当前行比较。若它返回多行,PostgreSQL 会报错。

集合子查询可以返回多行,常与 IN、EXISTS 等条件配合。放在 FROM 中的子查询会形成临时结果集,必须设置别名,外层查询再像读取普通表一样使用它。

完整示例数据

下面创建商品和销售记录,并连续演示三种位置的子查询。

sql
CREATE TEMP TABLE sub_product (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

CREATE TEMP TABLE sub_sale (
    id INT PRIMARY KEY,
    product_id INT NOT NULL REFERENCES sub_product(id),
    quantity INT NOT NULL
);

INSERT INTO sub_product VALUES
    (1, '键盘', 200.00),
    (2, '鼠标', 80.00),
    (3, '支架', 50.00);

INSERT INTO sub_sale VALUES
    (101, 1, 1),
    (102, 2, 3),
    (103, 2, 2);

SELECT
    name,
    price,
    (SELECT AVG(price) FROM sub_product) AS avg_price
FROM sub_product
ORDER BY id;

SELECT name
FROM sub_product
WHERE id IN (
    SELECT product_id
    FROM sub_sale
    WHERE quantity >= 3
)
ORDER BY id;

SELECT
    p.name,
    s.total_quantity
FROM (
    SELECT product_id, SUM(quantity) AS total_quantity
    FROM sub_sale
    GROUP BY product_id
) AS s
INNER JOIN sub_product AS p
    ON p.id = s.product_id
ORDER BY p.id;

预期输出

text
  name  | price  |      avg_price
--------+--------+---------------------
 键盘   | 200.00 | 110.0000000000000000
 鼠标   |  80.00 | 110.0000000000000000
 支架   |  50.00 | 110.0000000000000000
(3 rows)

 name
------
 鼠标
(1 row)

 name | total_quantity
------+----------------
 键盘 |              1
 鼠标 |              5
(2 rows)

第一个查询在 SELECT 中使用标量子查询,为每个商品附加同一个平均价格。第二个查询使用集合子查询,找出存在单次销量不少于 3 的商品。第三个查询先在 FROM 子查询中汇总,再关联商品名称。

选择子查询还是连接

当查询逻辑是“判断是否存在”时,EXISTS 往往比返回完整集合更直接;当外层需要使用子查询的多列聚合结果时,放在 FROM 中更清晰。

子查询并不天然比连接慢或快,PostgreSQL 优化器可能重写执行计划。实际优化应使用 EXPLAIN 检查扫描方式和行数估算,而不是只凭语法形式判断。

常见边界

标量子查询必须确保最多返回一行,通常通过聚合、唯一条件或 LIMIT 1 实现,但 LIMIT 1 还应配合明确排序。使用 NOT IN 时要特别注意子查询中的 NULL,否则条件结果可能不是预期的真值。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

使用标量与集合子查询

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通