MariaDB 教程 / 多表 JOIN 时如何避免重复行与错误合计
多表 JOIN 后做聚合之前,先确定连接结果的一行代表什么。订单连接到明细后,订单属性会在每条明细上重复;每单只收一次的配送费,应按订单层级计算,或先把明细汇总成每单一行再连接。
区分客户、订单、明细与商品的事实
同时显示客户姓名、下单时间、商品名和数量,需要连接四张表。最终一行代表一条订单明细,而不是完整的一单。忽略这个区别,不仅会重复计算订单数,也会重复计算每单只收一次的费用或折扣。
| 值 | 原始层级 | 连接到明细后的状态 |
|---|---|---|
| customer_name | 客户 | 在每条匹配明细上重复显示 |
| shipping_fee | 订单 | 该订单每多一条明细,就多出现一次 |
| quantity × unit_price | 明细 | 可以作为每条明细的金额相加 |
| 商品当前价格 | 商品 | 不能替代购买当时的历史单价 |
关系与外键设计教程中的中文关系图说明了这些表的连接方向。需要保留哪些未匹配对象,可参阅 LEFT JOIN 教程。具体 JOIN 语法见 MariaDB 官方 JOIN 文档;本节重点则是用可核对的数据证明聚合单位正确。
准备历史成交价与每单配送费
以 course_user 连接学习服务器,先检查 SELECT VERSION(), DATABASE(); 和 SHOW TABLES;。本节独立使用 c28_customers、c28_products、c28_orders、c28_items 四表;已有同名对象时,先查看结构和记录,不要覆盖未知用途的数据。
在 MariaDB 客户端中执行完整代码。也可按照 SQL 文件与 SOURCE 教程保存为 UTF-8 文件,用实际路径加载。父表先于子表创建和填充,以下代码不依赖其他章节已经创建的表。
USE shopcourse;
CREATE TABLE c28_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c28_order_customer FOREIGN KEY (customer_id)
REFERENCES c28_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c28_item_order FOREIGN KEY (order_id)
REFERENCES c28_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c28_item_product FOREIGN KEY (product_id)
REFERENCES c28_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c28_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c28_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c28_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c28_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
姓名、城市与商品名保留原始韩文字符串。商品 1~4 分别为笔记本、圆珠笔、马克杯与台灯;客户 1、3 的城市表示首尔,2 表示釜山,4 表示大田。使用编号核对连接,不要自行翻译 SQL 字面量后再比较预期结果。
已支付订单 101、102、103 分别有两条、一条、两条明细,共五条。每单配送费都是 1000。这个小样本让您可以逐单观察某个值究竟在连接结果中出现几次。
沿关系键连接全部四张表
SELECT o.order_id,c.customer_name,p.product_name,i.quantity,i.unit_price,
i.quantity*i.unit_price AS line_total
FROM c28_orders o JOIN c28_customers c ON c.customer_id=o.customer_id
JOIN c28_items i ON i.order_id=o.order_id
JOIN c28_products p ON p.product_id=i.product_id
WHERE o.status='paid' ORDER BY o.order_id,i.product_id;
应得到五条已支付明细,line_total 依次为 7000.00、4500.00、8000.00、3500.00、16000.00。按订单核对:101 的商品金额为 7000+4500=11500,102 为 8000,103 为 3500+16000=19500,合计 39000。
商品名称从商品表读取,但金额使用明细中的 unit_price。这样目录现价日后变化,也不会改变旧订单的成交金额。如果改成 p.price 参与计算,就把当前属性混进了历史事实,结果即使暂时相同也不代表设计正确。
检查时同时显示 order_id、商品和数量,确认同一订单的多行确实是不同明细。不要因为订单号重复就直接去重,那会丢失合法的购买记录。
观察订单层级配送费被重复相加
SELECT COUNT(*) AS joined_rows,COUNT(DISTINCT o.order_id) AS orders_count,
SUM(o.shipping_fee) AS duplicated_shipping,SUM(i.quantity*i.unit_price) AS sales
FROM c28_orders o JOIN c28_items i ON i.order_id=o.order_id
WHERE o.status='paid';
结果应为 joined_rows=5、orders_count=3、duplicated_shipping=5000.00、sales=39000.00。商品金额按明细计算正确,但配送费原本每单一次,三单只应合计 3000.00。订单 101 和 103 各有两条明细,导致各多加一次配送费。
COUNT(DISTINCT o.order_id) 只修正订单数量,不会自动修正旁边的 SUM。每个聚合表达式都需要检查自己的单位,不能因为同一行里有一个 DISTINCT,就认为其他金额已经去重。
把配送费改成 SUM(DISTINCT o.shipping_fee) 也不对:三单金额恰好都为 1000,按“数值”去重后只剩一个 1000,总额会变成 1000.00。不同订单收取相同费用是合法事实,数值相同不代表同一笔费用。DISTINCT 不是重复汇总的通用补丁。
先把明细汇总成每单一行,再连接订单
SELECT COUNT(*) AS orders_count,SUM(o.shipping_fee) AS shipping_total,
SUM(d.goods_total) AS goods_total
FROM c28_orders o
JOIN (SELECT order_id,SUM(quantity*unit_price) AS goods_total
FROM c28_items GROUP BY order_id) d ON d.order_id=o.order_id
WHERE o.status='paid';
括号内的派生查询先按 order_id 汇总,生成每单一个 goods_total;别名 d 让外层可以引用这份结果。再连接回订单时,每单只对应一行,所以结果为 orders_count=3、shipping_total=3000.00、goods_total=39000.00。
现在增加一条合法明细,只会改变该订单的商品合计,不会让订单配送费再次出现。可以在独立练习事务中增加一条明细,比较两种汇总,再回滚恢复原数据;不要直接在生产订单中做这种试验。
这条查询使用 INNER JOIN,因此只统计有明细的已支付订单。如果有一单 paid 订单尚无明细,是否仍应计入订单数和配送费,需要先由报表定义决定。若必须保留,可改用 LEFT JOIN,并明确空商品合计的处理政策;这不是单纯为了让查询多返回一行的语法调整。
预聚合在这里用于证明金额计算正确,不应仅凭这个小样本就宣称查询更快。实际性能仍需要结合数据量、索引与执行计划测量。
每增加一次连接,都检查行数如何变化
逐张加入表,并显示关键编号与记录数。结果突然增长时,检查连接键是否非唯一、是否漏掉条件,以及关系是否本来就允许多条匹配。先手工核对一笔小订单,再把规则扩展到整份报表。
如果同一订单分别连接两个明细集合,例如商品项与多条其他子记录,两个集合之间可能形成组合乘积。应分别把各集合汇总到需要的订单层级,再合并指标,而不是先把所有细节混在一起后补 DISTINCT。
本节查询不需要改写四张源表。清理前检查定义与记录,备份应保留的数据。下面只删除确认可丢弃的本节四表,顺序为子表在前、父表在后;DROP TABLE 会删除定义和全部行,普通 ROLLBACK 无法恢复,应放在事务实验之外。
DROP TABLE shopcourse.c28_items;
DROP TABLE shopcourse.c28_orders;
DROP TABLE shopcourse.c28_products;
DROP TABLE shopcourse.c28_customers;




