MySQL高级联结技巧：让你轻松掌握创建方法
以下内容将从概念出发，结合丰富的代码示例、图解与实操要点，帮助你深入理解并掌握 MySQL 中各种高级联结（JOIN）技巧。阅读过程中建议结合演练，以便更好地理解数据是如何“联结”在一起的。
1. 概述
- 联结（JOIN）：数据库中最常用的操作之一，用来将两个或多个表中的相关数据“按行”关联在一起查询。
随着数据模型变复杂，单纯的简单 INNER JOIN 已无法满足需求。本篇围绕 MySQL 的各种高级联结技巧展开，包括：
- 多表联结与复杂条件
- 自联结（Self-Join）
- 派生表（Derived Tables）与临时表结合联结
- LATERAL（横向联结）与 JSON\_TABLE（MySQL 8.0+）
- 联结优化策略：索引、执行计划与避免笛卡尔积
本文示例基于 MySQL 8.0，但绝大多数技巧也适用于 5.7 及更早版本。示例中的表结构与数据可根据自身业务进行调整。
2. 基础联结回顾（快速复习）
在进入高级技巧之前，先快速回顾四种最常见的联结类型（本节仅作背景铺垫，若已熟悉可跳过）。
2.1 INNER JOIN（内联结）
- 只返回在两个表中 匹配联结条件 的行。
语法：
SELECT a.*, b.* FROM table_a AS a INNER JOIN table_b AS b ON a.key = b.key;
示例表
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(20)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2)
);
INSERT INTO users VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Cathy');
INSERT INTO orders VALUES
(100, 1, 59.90),
(101, 1, 120.00),
(102, 3, 9.99);INNER JOIN 查询
SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount
FROM users AS u
INNER JOIN orders AS o
ON u.id = o.user_id;图解（INNER JOIN 匹配示意）
users orders
+------+-------+ +----+---------+--------+
| id | name | | id | user_id | amount |
+------+-------+ +----+---------+--------+
| 1 | Alice | |100 | 1 | 59.90 |
| 2 | Bob | |101 | 1 |120.00 |
| 3 | Cathy | |102 | 3 | 9.99 |
+------+-------+ +----+---------+--------+
内联结条件： u.id = o.user_id
匹配结果：
- u=1 ↔ o=100、o=101
- u=3 ↔ o=102
(u=2 无匹配记录被排除)结果集：
+---------+-------+----------+--------+
| user_id | name | order_id | amount |
+---------+-------+----------+--------+
| 1 | Alice | 100 | 59.90 |
| 1 | Alice | 101 | 120.00 |
| 3 | Cathy | 102 | 9.99 |
+---------+-------+----------+--------+2.2 LEFT JOIN（左联结）
- 返回 左表 中所有行，以及右表中匹配的行；如果右表无匹配，则对应列返回 NULL。
语法：
SELECT a.*, b.* FROM table_a AS a LEFT JOIN table_b AS b ON a.key = b.key;
LEFT JOIN 示例
SELECT u.id AS user_id, u.name, o.id AS order_id, o.amount
FROM users AS u
LEFT JOIN orders AS o
ON u.id = o.user_id;图解（LEFT JOIN 匹配示意）
左表 users 右表 orders
+------+-------+ +----+---------+--------+
| id | name | | id | user_id | amount |
+------+-------+ +----+---------+--------+
| 1 | Alice | |100 | 1 | 59.90 |
| 2 | Bob | |101 | 1 |120.00 |
| 3 | Cathy | |102 | 3 | 9.99 |
+------+-------+ +----+---------+--------+
左联结条件： u.id = o.user_id
结果：
- u=1 ↔ o=100、o=101
- u=2 ↔ 无匹配 → order_id=NULL, amount=NULL
- u=3 ↔ o=102结果集：
+---------+-------+----------+--------+
| user_id | name | order_id | amount |
+---------+-------+----------+--------+
| 1 | Alice | 100 | 59.90 |
| 1 | Alice | 101 | 120.00 |
| 2 | Bob | NULL | NULL |
| 3 | Cathy | 102 | 9.99 |
+---------+-------+----------+--------+2.3 RIGHT JOIN（右联结）
- 与 LEFT JOIN 对称：返回 右表 所有行，以及左表中匹配的行；若左表无匹配，左表字段为 NULL。
- 在 MySQL 中不如 LEFT JOIN 常用，一般可通过互换顺序转换为 LEFT JOIN。
2.4 CROSS JOIN（交叉联结 / 笛卡尔积）
- 不需要
ON条件，将左表的每一行与右表的每一行 完全 匹配，结果行数 = 行数A × 行数B。 语法：
SELECT * FROM table_a CROSS JOIN table_b;- 多用于生成辅助组合、统计笛卡尔积等；若无意中漏写联结条件，会导致数据量骤增。
3. 高级联结技巧
下面开始深入探讨若干在日常业务中极为实用的“高级联结”技巧。配合完整示例和图解，帮助你迅速上手，并在实际项目中灵活运用。
3.1 多条件与多列联结
当联结条件不止一列时，可以在 ON 中使用多个表达式，并且支持较多复杂表达式（比如范围、计算等）。
示例：多列联结
假设有两张表，一张 products，一张 inventory，它们需要根据 product_id 和 warehouse_id 同时匹配。
CREATE TABLE products (
product_id INT,
warehouse_id INT,
product_name VARCHAR(50),
PRIMARY KEY (product_id, warehouse_id)
);
CREATE TABLE inventory (
product_id INT,
warehouse_id INT,
stock INT,
PRIMARY KEY (product_id, warehouse_id)
);
INSERT INTO products VALUES
(1, 10, '笔记本'),
(1, 20, '笔记本（备用）'),
(2, 10, '鼠标'),
(3, 30, '键盘');
INSERT INTO inventory VALUES
(1, 10, 100),
(1, 20, 50),
(2, 10, 200);查询“每个产品在对应仓库的库存”
SELECT
p.product_id,
p.warehouse_id,
p.product_name,
i.stock
FROM products AS p
LEFT JOIN inventory AS i
ON p.product_id = i.product_id
AND p.warehouse_id = i.warehouse_id;图解（多列联结示意）
products inventory
+-----------+--------------+ +-----------+--------------+-------+
| product_id| warehouse_id | | product_id| warehouse_id | stock |
+-----------+--------------+ +-----------+--------------+-------+
| 1 | 10 | | 1 | 10 | 100 |
| 1 | 20 | | 1 | 20 | 50 |
| 2 | 10 | | 2 | 10 | 200 |
| 3 | 30 | +-----------+--------------+-------+
+-----------+--------------+
条件： p.product_id = i.product_id AND p.warehouse_id = i.warehouse_id
结果：
- (1,10) ↔ (1,10) → stock=100
- (1,20) ↔ (1,20) → stock=50
- (2,10) ↔ (2,10) → stock=200
- (3,30) ↔ 无匹配 → stock=NULL结果集：
+------------+--------------+--------------+-------+
| product_id | warehouse_id | product_name | stock |
+------------+--------------+--------------+-------+
| 1 | 10 | 笔记本 | 100 |
| 1 | 20 | 笔记本（备用）| 50 |
| 2 | 10 | 鼠标 | 200 |
| 3 | 30 | 键盘 | NULL |
+------------+--------------+--------------+-------+3.2 自联结（Self-Join）
自联结指的是一张表与自身做联结，用途非常广泛，比如查询层级关系（员工表查询上级/下级）、查找成对数据、时间序列相邻记录对比等。
示例 1：查找员工表中每个员工对应的直属上级
假设有一个 employees 表，结构如下：
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT -- 指向同一表的 id 列
);
INSERT INTO employees VALUES
(1, '总经理', NULL),
(2, '部门经理A', 1),
(3, '部门经理B', 1),
(4, '员工甲', 2),
(5, '员工乙', 2),
(6, '员工丙', 3);manager_id字段指明该员工的上级是谁（根节点的manager_id为 NULL）。
查询“每个员工及其上级姓名”
SELECT
e.id AS employee_id,
e.name AS employee_name,
m.id AS manager_id,
m.name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
ON e.manager_id = m.id;图解（自联结示意）
employees (e) employees (m)
+----+-----------+------------+ +----+-----------+------------+
| id | name | manager_id | | id | name | manager_id |
+----+-----------+------------+ +----+-----------+------------+
| 1 | 总经理 | NULL | | 1 | 总经理 | NULL |
| 2 | 部门经理A | 1 | | 2 | 部门经理A | 1 |
| 3 | 部门经理B | 1 | | 3 | 部门经理B | 1 |
| 4 | 员工甲 | 2 | | 4 | 员工甲 | 2 |
| 5 | 员工乙 | 2 | | 5 | 员工乙 | 2 |
| 6 | 员工丙 | 3 | | 6 | 员工丙 | 3 |
+----+-----------+------------+ +----+-----------+------------+
联结： e.manager_id = m.id
结果示例：
- e=1 → m=NULL
- e=2 → m=1
- e=3 → m=1
- e=4 → m=2
- ...结果集：
+-------------+---------------+------------+--------------+
| employee_id | employee_name | manager_id | manager_name |
+-------------+---------------+------------+--------------+
| 1 | 总经理 | NULL | NULL |
| 2 | 部门经理A | 1 | 总经理 |
| 3 | 部门经理B | 1 | 总经理 |
| 4 | 员工甲 | 2 | 部门经理A |
| 5 | 员工乙 | 2 | 部门经理A |
| 6 | 员工丙 | 3 | 部门经理B |
+-------------+---------------+------------+--------------+示例 2：查询同一个表中相邻时间戳的记录差值
假设有一张 events 表，记录系统的时间序列数据，需要计算两条相邻记录的时间差（或者数值差）。
CREATE TABLE events (
id INT PRIMARY KEY AUTO_INCREMENT,
sensor_id INT,
recorded_at DATETIME,
value DECIMAL(10,2)
);
INSERT INTO events (sensor_id, recorded_at, value) VALUES
(100, '2025-06-07 10:00:00', 20.5),
(100, '2025-06-07 10:05:00', 21.0),
(100, '2025-06-07 10:10:00', 20.8),
(200, '2025-06-07 10:00:00', 15.0),
(200, '2025-06-07 10:07:00', 16.2);查询“每条记录与上一条记录的时间差（秒）”
SELECT
curr.id AS curr_id,
curr.sensor_id AS sensor,
curr.recorded_at AS curr_time,
prev.recorded_at AS prev_time,
TIMESTAMPDIFF(SECOND, prev.recorded_at, curr.recorded_at) AS diff_seconds
FROM events AS curr
LEFT JOIN events AS prev
ON curr.sensor_id = prev.sensor_id
AND prev.recorded_at = (
SELECT MAX(recorded_at)
FROM events
WHERE sensor_id = curr.sensor_id
AND recorded_at < curr.recorded_at
);图解（相邻记录匹配）
events 表（简化视图） for sensor_id=100
+----+----------+---------------------+-------+
| id | sensor_id| recorded_at | value |
+----+----------+---------------------+-------+
| 1 | 100 | 2025-06-07 10:00:00 | 20.5 |
| 2 | 100 | 2025-06-07 10:05:00 | 21.0 |
| 3 | 100 | 2025-06-07 10:10:00 | 20.8 |
+----+----------+---------------------+-------+
对于 curr.id=2：prev = id=1
对于 curr.id=3：prev = id=2
diff_seconds:
- 对 id=2: TIMESTAMPDIFF => 300 (秒)
- 对 id=3: TIMESTAMPDIFF => 300 (秒)结果集（部分）：
+---------+--------+---------------------+---------------------+--------------+
| curr_id | sensor | curr_time | prev_time | diff_seconds |
+---------+--------+---------------------+---------------------+--------------+
| 1 | 100 | 2025-06-07 10:00:00 | NULL | NULL |
| 2 | 100 | 2025-06-07 10:05:00 | 2025-06-07 10:00:00 | 300 |
| 3 | 100 | 2025-06-07 10:10:00 | 2025-06-07 10:05:00 | 300 |
| 4 | 200 | 2025-06-07 10:00:00 | NULL | NULL |
| 5 | 200 | 2025-06-07 10:07:00 | 2025-06-07 10:00:00 | 420 |
+---------+--------+---------------------+---------------------+--------------+技巧点：
- 以上写法使用了子查询来获取 “上一条” 的
recorded_at。若数据量很大，效率不佳，可考虑使用窗口函数（MySQL 8.0+），如LAG(recorded_at) OVER (PARTITION BY sensor_id ORDER BY recorded_at)进行计算。
3.3 多表联结与派生表（Derived Tables）
实际业务场景中，经常需要对多张表进行联结，还可能结合子查询产生的结果再做联结。此时，可使用 派生表（Derived Table） 或 公共表表达式（CTE，MySQL 8.0+） 先对某些中间结果做汇总或筛选，再与其它表联结。
3.3.1 使用派生表
假设有三张表：orders、order_items、products，需要查询“每个用户在过去 30 天内购买金额最大的那一笔订单详情”。
-- 1. orders 表：用户每笔订单的元信息
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
created_at DATETIME
);
-- 2. order_items 表：订单中的商品明细
CREATE TABLE order_items (
id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
unit_price DECIMAL(10,2)
);
-- 3. products 表：商品信息
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(50),
category VARCHAR(20)
);步骤拆分与派生表思路
- 先计算每笔订单的总金额：在
order_items表上进行汇总，得到order_id→order_total。 - 筛选过去 30 天内每个用户的最大订单：将上一步得到的总金额与
orders表联结，按user_id分组取MAX(order_total)。 - 最终联结商品明细与产品信息，展示完整详情。
具体实现
-- 步骤 1：派生表 A：每笔订单的总金额
SELECT
oi.order_id,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM order_items AS oi
GROUP BY oi.order_id;
-- 步骤 2：派生表 B：过去 30 天内每个用户的最大订单
SELECT
o.user_id,
o.id AS order_id,
sub.order_total
FROM orders AS o
JOIN (
SELECT
oi.order_id,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM order_items AS oi
GROUP BY oi.order_id
) AS sub
ON o.id = sub.order_id
WHERE o.created_at >= NOW() - INTERVAL 30 DAY
-- 先筛选最近 30 天的订单
) AS t_order_totals
-- 再从 t_order_totals 中选出每个 user_id 的最大 order_total
-- 注意：这里可用子查询或派生表二次汇总，也可组合窗口函数简化
SELECT
user_id,
order_id,
order_total
FROM (
SELECT
t.user_id,
t.order_id,
t.order_total,
ROW_NUMBER() OVER (PARTITION BY t.user_id ORDER BY t.order_total DESC) AS rn
FROM (
-- 包含最近 30 天订单及其总金额
SELECT
o.user_id,
o.id AS order_id,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.created_at >= NOW() - INTERVAL 30 DAY
GROUP BY o.user_id, o.id
) AS t
) AS ranked_orders
WHERE rn = 1;上面用了多层派生表（内部叠加了窗口函数）。假如你的 MySQL 5.7 不支持窗口函数，也可拆分成多个派生表：
-- A: 每笔订单总额
SELECT
oi.order_id,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM order_items AS oi
GROUP BY oi.order_id
INTO TEMPORARY TABLE temp_order_totals;
-- B: 最近 30 天订单 + 总额
SELECT
o.user_id,
o.id AS order_id,
tot.order_total
FROM orders AS o
JOIN temp_order_totals AS tot
ON o.id = tot.order_id
WHERE o.created_at >= NOW() - INTERVAL 30 DAY
INTO TEMPORARY TABLE temp_recent_totals;
-- C: 每个用户最大订单
SELECT
user_id,
MAX(order_total) AS max_total
FROM temp_recent_totals
GROUP BY user_id
INTO TEMPORARY TABLE temp_user_max;
-- D: 将最大订单回联 recent_totals，获取 order_id
SELECT
r.user_id,
r.order_id,
r.order_total
FROM temp_recent_totals AS r
JOIN temp_user_max AS m
ON r.user_id = m.user_id
AND r.order_total = m.max_total
INTO TEMPORARY TABLE temp_user_best_order;
-- E: 最后联结 products，展示详情
SELECT
ubo.user_id,
ubo.order_id,
ubo.order_total,
p.id AS product_id,
p.name AS product_name,
oi.quantity,
oi.unit_price
FROM temp_user_best_order AS ubo
JOIN order_items AS oi
ON ubo.order_id = oi.order_id
JOIN products AS p
ON oi.product_id = p.id;技巧点：
- 利用临时表或派生表分步计算，可显著降低单次查询的复杂度，便于调试与性能分析。
- MySQL 8.0 支持 CTE（
WITH），可将上面多次派生表逻辑简化为一次完整的WITH ... SELECT语句，并且根据优化器可以更好地优化执行计划。
3.4 LATERAL（横向联结）与 JSON\_TABLE（MySQL 8.0+）
MySQL 8.0 引入了对 LATERAL 关键字的支持，使得可以在联结时引用左侧查询的列，从而“横向”生成新的行。例如：需要对 JSON 列进行拆分并联结到父表。
示例：将 JSON 数组拆分为多行并联结
假设有一张 invoices 表，列中包含一个 JSON 数组，记录订单的附加费用明细（每个元素含 type/amount）：
CREATE TABLE invoices (
id INT PRIMARY KEY,
user_id INT,
total DECIMAL(10,2),
fees JSON
);
INSERT INTO invoices (id, user_id, total, fees) VALUES
(1, 101, 100.00,
'[
{"type": "shipping", "amount": 10.00},
{"type": "tax", "amount": 8.00}
]'
),
(2, 102, 200.00,
'[
{"type": "shipping", "amount": 12.00},
{"type": "tax", "amount": 16.00},
{"type": "discount", "amount": -5.00}
]');需求：将每张发票的 fees JSON 数组拆分为多行，方便统计各类型费用总额
- 传统 MySQL 在拆分 JSON 时需要借助存储过程或临时表；MySQL 8.0+ 提供了
JSON_TABLE函数，结合LATERAL，能非常简洁地做到这一点。
SELECT
inv.id AS invoice_id,
inv.user_id,
jt.fee_type,
jt.fee_amount
FROM invoices AS inv
JOIN JSON_TABLE(
inv.fees,
"$[*]"
COLUMNS (
fee_type VARCHAR(20) PATH "$.type",
fee_amount DECIMAL(10,2) PATH "$.amount"
)
) AS jt
ON TRUE;JSON_TABLE作用：将 JSON 数组inv.fees转换为一个虚拟表jt，每个数组元素映射为一行，并可通过COLUMNS定义要提取的字段。ON TRUE：因为JSON_TABLE本身已经横向展开，等价于LATERAL。也可以写作JOIN LATERAL JSON_TABLE(...) AS jt ON TRUE。
图解（JSON\_TABLE 横向联结）
invoices JSON_TABLE(inv.fees)
+----+---------+---------+--------------------------------------+ +-----------+------------+
| id | user_id | total | fees (JSON) | | fee_type | fee_amount |
+----+---------+---------+--------------------------------------+ +-----------+------------+
| 1 | 101 | 100.00 | [ {"type":"shipping","amount":10}, | | shipping | 10.00 |
| | | | {"type":"tax","amount":8} ] | | tax | 8.00 |
| 2 | 102 | 200.00 | [ {"type":"shipping","amount":12}, | +-----------+------------+
| | | | {"type":"tax","amount":16}, |
| | | | {"type":"discount","amount":-5} ] | -> 对应展开出每条费用记录
+----+---------+---------+--------------------------------------+ 结果集：
+------------+---------+------------+------------+
| invoice_id | user_id | fee_type | fee_amount |
+------------+---------+------------+------------+
| 1 | 101 | shipping | 10.00 |
| 1 | 101 | tax | 8.00 |
| 2 | 102 | shipping | 12.00 |
| 2 | 102 | tax | 16.00 |
| 2 | 102 | discount | -5.00 |
+------------+---------+------------+------------+技巧点：
JSON_TABLE结合LATERAL（可选关键字）非常适合将嵌套或数组类型转为关系型行。- 若不想引入
LATERAL，可直接使用CROSS JOIN JSON_TABLE(...)，因为JSON_TABLE默认对每行invoices都横向展开。
3.5 窗口函数（Window Functions）结合联结
MySQL 8.0+ 支持窗口函数，可以在联结查询中避免使用子查询或自联结来获取“第一/最后一条记录”、“排名”等需求。示例如下。
示例：联结每个用户的“最新订单”
假设有两张表：users 和 orders，需要查询每个用户最近提交的一笔订单信息。
SELECT
u.id AS user_id,
u.name AS user_name,
o.id AS order_id,
o.created_at AS order_time,
o.amount
FROM users AS u
LEFT JOIN (
SELECT
id,
user_id,
amount,
created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
) AS o
ON u.id = o.user_id
AND o.rn = 1;- 通过
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)给每个用户的订单按时间降序编号，最新的订单编号为 1。 - 然后在外层联结时只保留
rn = 1的行，即可拿到每个用户最新的订单。
4. 复杂多表联结示例
4.1 多表同时联结（INNER + LEFT + 自联结 + 派生表）
有时需要同时对多张结构不同、需求不同的表进行混合联结。下面通过一组假设的表场景展示综合示例。
表结构
users：用户信息
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), signup_date DATE );orders：订单表
CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT, created_at DATETIME, status VARCHAR(20) );order\_items：订单明细
CREATE TABLE order_items ( id INT PRIMARY KEY, order_id INT, product_id INT, quantity INT, unit_price DECIMAL(10,2) );products：商品信息
CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), category VARCHAR(30), price DECIMAL(10,2) );reviews：商品评价
CREATE TABLE reviews ( id INT PRIMARY KEY, product_id INT, user_id INT, rating INT, -- 1-5 星 review_date DATE );
需求：
- 查询所有 2025 年上半年（2025-01-01 到 2025-06-30） 注册的用户。
- 对这些用户，显示他们最新一次已完成（
status = 'completed'）订单的总金额，以及该订单中各商品的名称与购买数量。 - 同时，如果用户对该订单中的商品有评价（
reviews表里存在对应product_id且user_id= 用户 ID），将评价星级也一并显示；否则用NULL占位。 - 如果用户到目前为止尚未完成任何订单，则以 NULL 显示对应的订单与商品信息。
分析思路：
- 筛选最近注册用户 → 在
users表直接用WHERE signup_date BETWEEN ...。 - 获得每位用户最新一次已完成订单 → 在
orders表使用窗口函数（或派生表 + 自联结）得到每个用户最新completed状态订单的order_id。 - 计算该订单总金额 → 在
order_items表对该订单进行聚合，得到order_total。 - 获取订单中的商品明细 → 在
order_items与products表做 INNER JOIN。 - 将评价信息联结进来 → 在
products与reviews表上做 LEFT JOIN，条件为product_id与user_id同时匹配。 - 若用户无任何已完成订单 → 最终做
users LEFT JOIN外层所有步骤，以保证用户全部展示。
步骤拆解
步骤 2：获取最新已完成订单（窗口函数示例）
WITH latest_completed AS (
SELECT
id AS order_id,
user_id,
created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
WHERE status = 'completed'
)
-- 将 CTE 用于后续联结步骤 3：合并订单总金额
SELECT
lc.user_id,
lc.order_id,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM latest_completed AS lc
JOIN order_items AS oi
ON lc.order_id = oi.order_id
WHERE lc.rn = 1 -- 只保留最新一笔 completed 订单
GROUP BY lc.user_id, lc.order_id将上面结果命名为 user_latest_orders。
步骤 4 & 5：订单商品明细 + 评价
SELECT
ulo.user_id,
ulo.order_id,
ulo.order_total,
p.id AS product_id,
p.name AS product_name,
oi.quantity AS purchased_qty,
r.rating AS user_rating
FROM (
-- user_latest_orders CTE/派生
SELECT
lc.user_id,
lc.order_id,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM (
SELECT
id AS order_id,
user_id,
created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
WHERE status = 'completed'
) AS lc
JOIN order_items AS oi
ON lc.order_id = oi.order_id
WHERE lc.rn = 1
GROUP BY lc.user_id, lc.order_id
) AS ulo
JOIN order_items AS oi
ON ulo.order_id = oi.order_id
JOIN products AS p
ON oi.product_id = p.id
LEFT JOIN reviews AS r
ON p.id = r.product_id
AND r.user_id = ulo.user_id;最终与用户表做 LEFT JOIN
SELECT
u.id AS user_id,
u.name AS user_name,
ulo.order_id,
ulo.order_total,
p.product_id,
p.product_name,
ulo_items.purchased_qty,
ulo_items.user_rating
FROM users AS u
LEFT JOIN (
-- 这是上一步得到的用户与商品明细 + 评价
SELECT
ulo.user_id,
ulo.order_id,
ulo.order_total,
p.id AS product_id,
p.name AS product_name,
oi.quantity AS purchased_qty,
r.rating AS user_rating
FROM (
-- user_latest_orders 计算
SELECT
lc.user_id,
lc.order_id,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM (
SELECT
id AS order_id,
user_id,
created_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
WHERE status = 'completed'
) AS lc
JOIN order_items AS oi
ON lc.order_id = oi.order_id
WHERE lc.rn = 1
GROUP BY lc.user_id, lc.order_id
) AS ulo
JOIN order_items AS oi
ON ulo.order_id = oi.order_id
JOIN products AS p
ON oi.product_id = p.id
LEFT JOIN reviews AS r
ON p.id = r.product_id
AND r.user_id = ulo.user_id
) AS ulo_items
ON u.id = ulo_items.user_id
WHERE u.signup_date BETWEEN '2025-01-01' AND '2025-06-30'
ORDER BY u.id, ulo_items.order_id, p.category;整体图解（简化示意，多表联结流程）
users (过滤 2025-01-01 ~ 2025-06-30 注册)
│
│ LEFT JOIN （步骤 1+2+3+4+5 合并结果）
│
▼
user_latest_order_items_with_reviews
├─ 用户最新已完成订单（窗口函数 + 聚合）
├─ 订单商品明细（order_items ↔ products）
└─ 联结评价（products ↔ reviews，LEFT JOIN 保证无评价也显示）5. 联结优化策略
当联结变得非常复杂、涉及多张大表时，查询性能成为关键。以下是一些常见的优化建议与技巧。
5.1 使用合适的索引
联结字段需建索引
- 对
ON a.col = b.col中的列最好建立索引。 - 若是多列联结（如
(a.col1, a.col2) = (b.col1, b.col2)），可考虑组合索引(col1, col2)，提高匹配效率。
- 对
避免在联结条件中使用函数或表达式
-- 不推荐（索引失效） ON DATE(a.created_at) = b.some_date -- 推荐 ON a.created_date = b.some_date AND a.created_time >= '00:00:00'尽量将表达式移到查询外层或用派生列预处理，以免 MySQL 无法利用索引。
5.2 小心笛卡尔积
- 无条件联结 或者
JOIN时忘记写ON，会导致笛卡尔积，行数急剧膨胀，严重影响性能。 在多次联结时，务必逐个确认联结条件。例如：
SELECT * FROM A JOIN B -- ← 若忘写 ON，直接与 B 做 CROSS JOIN（笛卡尔积） JOIN C ON ... -- 此时 A×B × C 的匹配，效率非常低
5.3 控制中间结果集大小
先筛选、后联结（Push-down Predicate）
- 在能提前过滤的表上先做
WHERE或者在派生表里做聚合、筛选，避免一次性联结后再做过滤。 - 例如：若只需最近 30 天的订单，就先在
orders表WHERE created_at >= NOW() - INTERVAL 30 DAY，再与其它表联结。
- 在能提前过滤的表上先做
使用
EXISTS或者子查询限制行数- 对于某些不需要全部列联结而只是判断是否存在，可以使用
EXISTS或半联结（Semi-Join）提升性能。
SELECT u.* FROM users AS u WHERE EXISTS ( SELECT 1 FROM orders AS o WHERE o.user_id = u.id AND o.status = 'completed' );- 对于某些不需要全部列联结而只是判断是否存在，可以使用
限制行数（
LIMIT+ 排序）- 对分页查询或只需要前 N 条记录的场景，尽早使用
LIMIT并配合索引避免全表扫描。
- 对分页查询或只需要前 N 条记录的场景，尽早使用
5.4 查看执行计划（EXPLAIN）
在编写复杂联结前，务必用
EXPLAIN（或EXPLAIN ANALYZE）预览执行计划：EXPLAIN FORMAT=JSON SELECT ... FROM ... JOIN ...;关注重点：
- type 应尽量为
ref、range或eq_ref，避免ALL（全表扫描）。 - possible\_keys 和 key：确保联结字段对应的索引被使用。
- rows 估算：若某一步骤需要扫描大量行，考虑提前加筛选条件或改写逻辑。
- type 应尽量为
6. 常见注意事项与最佳实践
明确表别名
- 在多张表联结时，一定要为表起有意义的别名，便于阅读与维护。
- 如
users AS u、orders AS o、order_items AS oi。
避免
SELECT *- 明确列出所需字段，减少网络传输和服务器 I/O 开销。
- 对于较多列的表，可以使用
SELECT u.id, u.name, o.id, SUM(oi.quantity * oi.unit_price) AS total这种写法。
使用
STRAIGHT_JOIN强制指定联结顺序（谨慎）- MySQL 优化器会自动选择联结顺序。但在某些特殊场景下，优化器选择不理想，可用
STRAIGHT_JOIN强制让表按 SQL 书写顺序联结。 - 注意：此方式需极度谨慎，仅当确认优化器选择确实不理想时再考虑。
- MySQL 优化器会自动选择联结顺序。但在某些特殊场景下，优化器选择不理想，可用
合理拆分业务逻辑
- 当单条 SQL 变得极度复杂时，考虑将其拆分到多个步骤（临时表/派生表/ETL流程）中完成，既利于调试，也能让执行计划更清晰。
利用覆盖索引（Covering Index）
如果联结后的查询字段都包含在某个索引中，可减少回表操作，提升查询效率。例如：
CREATE INDEX idx_orders_user_status ON orders (user_id, status, created_at, id);- 若查询中用到的字段都在上述索引中，则 MySQL 仅扫描索引即可完成 SELECT。
7. 小结
本文围绕 MySQL 中的高级联结技巧，从基础 JOIN 类型回顾出发，逐步深入到“多列联结”、“自联结”、“派生表（Derived Tables）与 CTE”、“LATERAL 与 JSON\_TABLE”、“窗口函数结合联结”及“多表综合示例”等多个方面，并讲解了联结优化策略与常见注意事项。核心要点如下：
- 多列与多条件联结：可在
ON中写任意布尔表达式，有利于精确匹配。 - 自联结（Self-Join）：适用于层级结构、相邻记录比对等需求，通过将同一表起不同别名实现“自身与自身联结”。
- 派生表 / CTE：在联结前对中间结果进行预处理（聚合、筛选、排序、窗口函数编号），再与其它表做联结，既清晰又易调试。
- LATERAL 与 JSON\_TABLE：MySQL 8.0+ 支持对 JSON 字段进行横向拆分，并与父表数据联结。
- 窗口函数 + 联结：通过
ROW_NUMBER() / RANK() OVER (...)等，能快速实现“最新一条”/“排名”类联结需求，效率高于传统子查询方式。 - 优化策略：为联结字段加索引、避免笛卡尔积、提前筛选、合理拆分查询、查看执行计划（EXPLAIN），是提升性能的关键。
掌握这些技巧后，你能轻松应对各种复杂联结场景，并在实际项目中写出高效、可维护的 SQL。
评论已关闭