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,否则条件结果可能不是预期的真值。
按自己的节奏,确认真正理解。