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。它只能删除完全相同的行,无法修复错误的连接条件,也可能掩盖数据粒度问题。

排查时先单独查询连接后的主键组合,例如订单编号和商品编号,再确认每一步的行数是否符合一对一或一对多关系。生产环境还应为外键列建立索引,减少多表连接的扫描成本。

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

目录与阅读设置

阅读设置

17

记录一个想法

使用多表连接

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

登录哪都通