PostgreSQL / 系统课时
使用多表连接
使用多表连接
本课解决“如何按业务依赖连接三张以上表”的问题。你将从订单、明细和商品关系出发,掌握连接顺序,并用聚合把多条明细还原为每张订单一行。
学习目标
- 按外键依赖顺序连接多张表
- 识别一对多连接导致的行数扩张
- 使用分组聚合得到正确业务结果
先画出连接路径
多表连接不是把所有表随意写在一起,而是沿着明确的关联路径逐步扩展。例如客户关联订单,订单关联订单明细,订单明细再关联商品。
每增加一张“一对多”表,结果行数都可能增加。订单包含两条明细时,订单字段会重复出现两次,这不是数据库错误,而是连接后的真实粒度变成了“订单明细”。
完整查询示例
下面计算每张订单的客户、商品种类数和订单总额。查询先连接明细与商品,再按订单分组。
sql
CREATE TEMP TABLE multi_customer (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TEMP TABLE multi_order (
id INT PRIMARY KEY,
customer_id INT NOT NULL REFERENCES multi_customer(id)
);
CREATE TEMP TABLE multi_product (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
price NUMERIC(10, 2) NOT NULL
);
CREATE TEMP TABLE multi_order_item (
order_id INT REFERENCES multi_order(id),
product_id INT REFERENCES multi_product(id),
quantity INT NOT NULL CHECK (quantity > 0),
PRIMARY KEY (order_id, product_id)
);
INSERT INTO multi_customer VALUES
(1, '李雷'),
(2, '韩梅梅');
INSERT INTO multi_order VALUES
(1001, 1),
(1002, 2);
INSERT INTO multi_product VALUES
(10, '键盘', 199.00),
(11, '鼠标', 89.00),
(12, '支架', 60.00);
INSERT INTO multi_order_item VALUES
(1001, 10, 1),
(1001, 11, 2),
(1002, 12, 3);
SELECT
o.id AS order_id,
c.name AS customer_name,
COUNT(oi.product_id) AS product_types,
SUM(p.price * oi.quantity) AS total_amount
FROM multi_order AS o
INNER JOIN multi_customer AS c
ON c.id = o.customer_id
INNER JOIN multi_order_item AS oi
ON oi.order_id = o.id
INNER JOIN multi_product AS p
ON p.id = oi.product_id
GROUP BY o.id, c.name
ORDER BY o.id;预期输出
text
order_id | customer_name | product_types | total_amount
----------+---------------+---------------+-------------
1001 | 李雷 | 2 | 377.00
1002 | 韩梅梅 | 1 | 180.00
(2 rows)订单 1001 有两条明细,因此连接后的中间结果有两行;GROUP BY 再把它们合并为一张订单。SUM 使用商品单价乘数量,得到 199 + 89 × 2 = 377。
避免错误重复
不要为了消除重复直接添加 DISTINCT。它只能删除完全相同的行,无法修复错误的连接条件,也可能掩盖数据粒度问题。
排查时先单独查询连接后的主键组合,例如订单编号和商品编号,再确认每一步的行数是否符合一对一或一对多关系。生产环境还应为外键列建立索引,减少多表连接的扫描成本。
按自己的节奏,确认真正理解。