MySQL 复杂关联查询实战

复杂关联查询的难点通常不是会不会写 join，而是：

一行结果究竟代表用户、订单、商品还是用户月份。
多条一对多关系同时关联后，行数是否被相乘。
过滤条件放在 on 还是 where。
聚合应该发生在 Join 前还是 Join 后。
“存在”“不存在”“全部满足”“最新一条”分别用什么模型。
时间有效区间、并列值、NULL 和分页语义是否正确。
数据量扩大后，执行计划会不会变成大量重复扫描。

本文沿用 SQL 查询面试题中的用户、商品、订单、订单明细、员工等表，并增加支付、退款、物流、价格历史和状态事件表。

复杂 Join 的第一原则：先确定结果粒度

写 SQL 前先完成一句话：

最终结果每一行代表什么？

目标	正确粒度
订单列表	一行一个订单
用户月度消费	一行一个用户月份
商品销量	一行一个商品
订单明细	一行一个订单明细
当前物流状态	一行一个订单或包裹

如果目标是一行一个订单，却直接 Join 订单明细、支付记录和退款记录，结果很可能不再是一行一个订单。

关联基数决定结果行数

mermaid

flowchart LR
    U["用户 1"] --> O["订单 N"]
    O --> I["订单明细 N"]
    O --> P["支付尝试 N"]
    P --> R["退款记录 N"]
    O --> S["物流记录 N"]

常见关系：

用户到订单：一对多。
订单到明细：一对多。
订单到支付尝试：一对多。
支付到退款：一对多。
订单到物流：可能一对多。

假设某订单有 3 条明细、2 条支付记录和 2 条退款记录，直接同时 Join 后可能产生：

text

3 × 2 × 2 = 12 行

此时再 sum(order.total_amount)、sum(item_amount) 或 sum(refund_amount)，金额就可能被重复累计。

扩展表结构

sql

create table payments (
  id bigint primary key comment '支付记录ID',
  order_id bigint not null comment '订单ID',
  payment_no varchar(64) not null comment '支付流水号',
  status varchar(20) not null comment '支付状态：PENDING-处理中，SUCCESS-成功，FAILED-失败',
  paid_amount decimal(18, 2) not null comment '支付金额',
  paid_at datetime comment '支付成功时间，未成功时为NULL',
  unique key uk_payments_no(payment_no),
  key idx_payments_order_status(order_id, status, paid_at, id),
  constraint fk_payments_order
    foreign key(order_id) references orders(id)
) engine = InnoDB default charset = utf8mb4 comment = '支付记录表，一张订单可有多次支付尝试';

create table refunds (
  id bigint primary key comment '退款记录ID',
  payment_id bigint not null comment '原支付记录ID',
  refund_no varchar(64) not null comment '退款流水号',
  status varchar(20) not null comment '退款状态：PENDING-处理中，SUCCESS-成功，FAILED-失败',
  refund_amount decimal(18, 2) not null comment '申请退款金额',
  created_at datetime not null comment '退款申请时间',
  unique key uk_refunds_no(refund_no),
  key idx_refunds_payment_status(payment_id, status, created_at, id),
  constraint fk_refunds_payment
    foreign key(payment_id) references payments(id)
) engine = InnoDB default charset = utf8mb4 comment = '退款记录表，一笔支付可分多次退款';

create table shipments (
  id bigint primary key comment '物流记录ID',
  order_id bigint not null comment '订单ID',
  shipment_no varchar(64) not null comment '物流单号',
  status varchar(20) not null comment '物流状态：CREATED-已创建，SHIPPED-已发货，DELIVERED-已签收',
  shipped_at datetime comment '发货时间，未发货时为NULL',
  updated_at datetime not null comment '物流记录最后更新时间',
  unique key uk_shipments_no(shipment_no),
  key idx_shipments_order_updated(order_id, updated_at desc, id desc),
  constraint fk_shipments_order
    foreign key(order_id) references orders(id)
) engine = InnoDB default charset = utf8mb4 comment = '订单物流表，一张订单可拆成多个包裹';

create table product_price_history (
  id bigint primary key comment '价格历史ID',
  product_id bigint not null comment '商品ID',
  price decimal(18, 2) not null comment '该有效期内的商品价格',
  valid_from datetime not null comment '价格生效时间，区间左边界',
  valid_to datetime comment '价格失效时间，区间右边界；NULL表示当前仍有效',
  key idx_price_product_from(product_id, valid_from, valid_to),
  constraint fk_price_product
    foreign key(product_id) references products(id),
  constraint chk_price_range
    check(valid_to is null or valid_to > valid_from)
) engine = InnoDB default charset = utf8mb4 comment = '商品价格历史表，有效期采用左闭右开区间';

create table order_status_events (
  id bigint primary key comment '状态事件ID',
  order_id bigint not null comment '订单ID',
  status varchar(20) not null comment '变更后的订单状态',
  event_time datetime not null comment '状态变更时间',
  key idx_events_order_time(order_id, event_time desc, id desc),
  constraint fk_events_order
    foreign key(order_id) references orders(id)
) engine = InnoDB default charset = utf8mb4 comment = '订单状态变更事件表';

create table external_settlements (
  order_id bigint primary key comment '外部系统订单ID',
  settlement_amount decimal(18, 2) not null comment '外部系统结算金额',
  settled_at datetime not null comment '结算完成时间'
) engine = InnoDB default charset = utf8mb4 comment = '外部结算表，用于演示跨系统集合对账';

扩展表关系速查

表名	中文名称	上游表	关联关系	主要练习场景
`payments`	支付记录表	`orders`	一张订单可有多次支付尝试	一对多预聚合、最新一次支付
`refunds`	退款记录表	`payments`	一笔支付可分多次退款	多层一对多、退款金额汇总
`shipments`	订单物流表	`orders`	一张订单可拆成多个包裹	每组最新一条、状态筛选
`product_price_history`	商品价格历史表	`products`	一个商品对应多个价格有效期	时态 Join、区间关联
`order_status_events`	订单状态事件表	`orders`	一张订单对应多个状态事件	事件流、最新状态、停留时长
`external_settlements`	外部结算表	外部系统订单	以订单 ID 与本地订单对账	模拟全外连接、差异集合

扩展表字段中文速查

支付和退款

表名	字段	类型	可空	中文含义与口径
`payments`	`id`	`bigint`	否	支付记录 ID，主键
`payments`	`order_id`	`bigint`	否	订单 ID，关联 `orders.id`
`payments`	`payment_no`	`varchar(64)`	否	支付流水号，全局唯一
`payments`	`status`	`varchar(20)`	否	支付状态：`PENDING` 处理中、`SUCCESS` 成功、`FAILED` 失败
`payments`	`paid_amount`	`decimal(18,2)`	否	本次支付金额
`payments`	`paid_at`	`datetime`	是	支付成功时间，未成功时为 `NULL`
`refunds`	`id`	`bigint`	否	退款记录 ID，主键
`refunds`	`payment_id`	`bigint`	否	原支付记录 ID，关联 `payments.id`
`refunds`	`refund_no`	`varchar(64)`	否	退款流水号，全局唯一
`refunds`	`status`	`varchar(20)`	否	退款状态：`PENDING` 处理中、`SUCCESS` 成功、`FAILED` 失败
`refunds`	`refund_amount`	`decimal(18,2)`	否	本次申请退款金额；统计实退金额时还要过滤成功状态
`refunds`	`created_at`	`datetime`	否	退款申请时间，不等同于退款成功时间

物流和商品价格历史

表名	字段	类型	可空	中文含义与口径
`shipments`	`id`	`bigint`	否	物流记录 ID，主键
`shipments`	`order_id`	`bigint`	否	订单 ID，关联 `orders.id`
`shipments`	`shipment_no`	`varchar(64)`	否	物流单号，全局唯一
`shipments`	`status`	`varchar(20)`	否	物流状态：`CREATED` 已创建、`SHIPPED` 已发货、`DELIVERED` 已签收
`shipments`	`shipped_at`	`datetime`	是	实际发货时间，未发货时为 `NULL`
`shipments`	`updated_at`	`datetime`	否	物流记录最后更新时间
`product_price_history`	`id`	`bigint`	否	价格历史 ID，主键
`product_price_history`	`product_id`	`bigint`	否	商品 ID，关联 `products.id`
`product_price_history`	`price`	`decimal(18,2)`	否	该有效期内的商品价格
`product_price_history`	`valid_from`	`datetime`	否	价格生效时间，左闭右开区间的左边界
`product_price_history`	`valid_to`	`datetime`	是	价格失效时间，右边界；`NULL` 表示当前仍有效

状态事件和外部结算

表名	字段	类型	可空	中文含义与口径
`order_status_events`	`id`	`bigint`	否	状态事件 ID，主键
`order_status_events`	`order_id`	`bigint`	否	订单 ID，关联 `orders.id`
`order_status_events`	`status`	`varchar(20)`	否	本次事件变更后的订单状态
`order_status_events`	`event_time`	`datetime`	否	状态变更发生时间
`external_settlements`	`order_id`	`bigint`	否	外部系统订单 ID，主键；不声明本地外键
`external_settlements`	`settlement_amount`	`decimal(18,2)`	否	外部系统确认的结算金额
`external_settlements`	`settled_at`	`datetime`	否	外部系统结算完成时间

模式一：多个一对多 Join 导致金额翻倍

错误写法

目标是一行一个订单，统计明细金额、支付金额和退款金额：

sql

select o.id,
       sum(oi.quantity * oi.unit_price) as item_amount,
       sum(p.paid_amount) as paid_amount,
       sum(r.refund_amount) as refund_amount
from orders o
left join order_items oi on oi.order_id = o.id
left join payments p on p.order_id = o.id
left join refunds r on r.payment_id = p.id
group by o.id;

这个 SQL 语法正确，但三条一对多关系会互相乘法展开，同一笔金额被重复累加。使用 sum(distinct amount) 也不是可靠修复：两条不同业务记录金额相同会被错误去重。

正确写法：各自预聚合后再关联

sql

with item_summary as (
  select order_id,
         sum(quantity * unit_price) as item_amount
  from order_items
  group by order_id
), payment_summary as (
  select order_id,
         sum(case when status = 'SUCCESS' then paid_amount else 0 end)
           as paid_amount
  from payments
  group by order_id
), refund_summary as (
  select p.order_id,
         sum(case when r.status = 'SUCCESS' then r.refund_amount else 0 end)
           as refund_amount
  from payments p
  join refunds r on r.payment_id = p.id
  group by p.order_id
)
select o.id,
       o.total_amount,
       coalesce(i.item_amount, 0) as item_amount,
       coalesce(p.paid_amount, 0) as paid_amount,
       coalesce(r.refund_amount, 0) as refund_amount
from orders o
left join item_summary i on i.order_id = o.id
left join payment_summary p on p.order_id = o.id
left join refund_summary r on r.order_id = o.id;

关键是每个 CTE 都先变成“一行一个订单”，再进行一对一关联，最终粒度保持稳定。

MySQL 5.7 可以把 CTE 改成派生表。CTE 主要改善表达，不保证一定物化或一定更快，应查看执行计划。

模式二：只判断是否存在时使用半连接

题目：查询存在成功支付的订单，不需要返回支付明细。

容易产生重复的写法

sql

select distinct o.*
from orders o
join payments p on p.order_id = o.id
where p.status = 'SUCCESS';

如果一个订单有多条成功支付记录，Join 会产生多行，再依赖 distinct 去重。

模式三：查询不存在关联记录使用反连接

题目：查询没有任何成功支付记录的订单。

sql

select o.id, o.user_id, o.created_at
from orders o
where not exists (
  select 1
  from payments p
  where p.order_id = o.id
    and p.status = 'SUCCESS'
);

也可以使用左连接：

sql

select o.id, o.user_id, o.created_at
from orders o
left join payments p
  on p.order_id = o.id
 and p.status = 'SUCCESS'
where p.id is null;

not in 子查询如果可能返回 NULL，会受到三值逻辑影响；反连接通常优先考虑 not exists。

模式四：每组最新一条关联记录

题目：每个订单只关联最后更新的一条物流记录。

MySQL 8 窗口函数

sql

with latest_shipment as (
  select s.*,
         row_number() over(
           partition by order_id
           order by updated_at desc, id desc
         ) as rn
  from shipments s
)
select o.id as order_id,
       o.status as order_status,
       s.shipment_no,
       s.status as shipment_status,
       s.updated_at
from orders o
left join latest_shipment s
  on s.order_id = o.id
 and s.rn = 1;

MySQL 5.7 反连接写法

sql

select o.id as order_id,
       s.shipment_no,
       s.status,
       s.updated_at
from orders o
left join shipments s
  on s.order_id = o.id
 and not exists (
   select 1
   from shipments newer
   where newer.order_id = s.order_id
     and (
       newer.updated_at > s.updated_at
       or (newer.updated_at = s.updated_at and newer.id > s.id)
     )
 );

排序必须追加唯一键，否则相同更新时间下“最新一条”不确定。

模式五：关联后取每组 Top N

题目：返回每个城市支付金额最高的 3 个用户，并列金额按用户 ID 稳定排序，严格每个城市最多三行。

sql

with user_paid as (
  select u.city,
         u.id as user_id,
         u.username,
         sum(o.total_amount) as paid_amount
  from users u
  join orders o on o.user_id = u.id
  where o.status = 'PAID'
  group by u.city, u.id, u.username
), ranked as (
  select up.*,
         row_number() over(
           partition by city
           order by paid_amount desc, user_id
         ) as rn
  from user_paid up
)
select city, user_id, username, paid_amount
from ranked
where rn <= 3
order by city, rn;

必须先聚合到“一行一个城市用户”，再排名。若题目要求同金额共享名次，应改用 dense_rank，并接受每个城市可能超过三行。

模式六：多对多关联

用户与商品通过订单和明细形成多对多关系：

text

users → orders → order_items → products

题目：查询购买过数据库分类商品的用户，用户只返回一次。

sql

select u.id, u.username
from users u
where exists (
  select 1
  from orders o
  join order_items oi on oi.order_id = o.id
  join products p on p.id = oi.product_id
  join categories c on c.id = p.category_id
  where o.user_id = u.id
    and o.status = 'PAID'
    and c.category_name = '数据库'
);

如果还要返回商品明细，就不能只用 exists，而应接受一行一个用户商品或先按用户聚合。先明确粒度，再选关联方式。

模式七：关系除法——全部满足

题目：查询买过指定分类下全部在售商品的用户。

计数法

sql

select o.user_id
from orders o
join order_items oi on oi.order_id = o.id
join products p on p.id = oi.product_id
where o.status = 'PAID'
  and p.category_id = ?
  and p.status = 'ACTIVE'
group by o.user_id
having count(distinct p.id) = (
  select count(*)
  from products target
  where target.category_id = ?
    and target.status = 'ACTIVE'
);

双重 NOT EXISTS

sql

select u.id, u.username
from users u
where not exists (
  select 1
  from products p
  where p.category_id = ?
    and p.status = 'ACTIVE'
    and not exists (
      select 1
      from orders o
      join order_items oi on oi.order_id = o.id
      where o.user_id = u.id
        and o.status = 'PAID'
        and oi.product_id = p.id
    )
);

双重 not exists 的语义是“不存在一个目标商品是该用户没有买过的”。还要和面试官确认：目标分类没有在售商品时，是否所有用户都算满足。逻辑上的空集全称命题与业务期望可能不同。

模式八：时态关联——查询当时有效的记录

价格历史表通过 [valid_from, valid_to) 表示有效区间。题目：找到订单创建时每个商品的目录价格。

sql

select o.id as order_id,
       oi.product_id,
       oi.unit_price as order_unit_price,
       ph.price as catalog_price_at_order_time
from orders o
join order_items oi on oi.order_id = o.id
join product_price_history ph
  on ph.product_id = oi.product_id
 and o.created_at >= ph.valid_from
 and (o.created_at < ph.valid_to or ph.valid_to is null);

必须保证同一商品的价格有效区间不重叠，否则同一订单明细会匹配多条价格记录。MySQL 普通唯一约束不能直接保证时间区间不重叠，通常要由写入事务、锁和业务校验维护。

索引候选：

sql

(product_id, valid_from, valid_to)

范围关联仍可能扫描多个候选版本，应限制历史规模并查看实际执行计划。

模式九：区间重叠关联

两个半开时间区间 [a_start, a_end) 和 [b_start, b_end) 重叠的条件是：

text

a_start < b_end AND a_end > b_start

假设需要查询促销活动与订单查询窗口是否重叠：

sql

select p.id as promotion_id,
       w.id as window_id
from promotions p
join report_windows w
  on p.start_time < w.end_time
 and p.end_time > w.start_time;

不能只判断开始时间是否落在另一个区间内，否则“一个区间完全覆盖另一个区间”时会漏匹配。

区间 Join 通常难以像等值 Join 一样高效。应先用租户、商品、区域等值条件缩小候选，再比较时间范围。

模式十：自关联与递归关联

只查直接经理

sql

select e.id,
       e.employee_name,
       m.id as manager_id,
       m.employee_name as manager_name
from employees e
left join employees m on m.id = e.manager_id;

查询全部上级链路

MySQL 8：

sql

with recursive manager_chain as (
  select e.id as node_id,
         e.employee_name,
         e.manager_id,
         0 as depth
  from employees e
  where e.id = ?

  union all

  select m.id as node_id,
         m.employee_name,
         m.manager_id,
         mc.depth + 1
  from manager_chain mc
  join employees m on m.id = mc.manager_id
  where mc.depth < 20
)
select *
from manager_chain
order by depth;

生产模型应防止经理关系形成环。深度上限只能止损，不能证明数据不存在环。

模式十一：模拟 FULL OUTER JOIN

MySQL 没有通用的 full outer join 语法，可以用“左连接全部结果 + 右侧未匹配结果”模拟。

假设核对内部订单 orders 和外部结算表 external_settlements：

sql

select o.id as order_id,
       s.order_id as settlement_order_id,
       o.total_amount,
       s.settlement_amount
from orders o
left join external_settlements s on s.order_id = o.id

union all

select o.id as order_id,
       s.order_id as settlement_order_id,
       o.total_amount,
       s.settlement_amount
from external_settlements s
left join orders o on o.id = s.order_id
where o.id is null;

第二部分只补充右侧未匹配数据，所以使用 union all 不会重复已匹配行。若使用两个完整左连接再 union，会产生不必要的去重成本。

模式十二：父表分页后再关联明细

目标：订单列表一页 20 个订单，同时返回明细。

错误写法

sql

select o.id, o.created_at, oi.product_id, oi.quantity
from orders o
join order_items oi on oi.order_id = o.id
order by o.created_at desc, o.id desc
limit 20;

这里限制的是 Join 后的 20 行明细，不是 20 个订单。一个订单有很多明细时，一页可能只有几个订单，订单还可能被截断。

先分页订单，再关联明细

sql

with page_orders as (
  select id, user_id, status, total_amount, created_at
  from orders
  where created_at < ?
     or (created_at = ? and id < ?)
  order by created_at desc, id desc
  limit 20
)
select po.id,
       po.user_id,
       po.status,
       po.total_amount,
       po.created_at,
       oi.product_id,
       oi.quantity,
       oi.unit_price
from page_orders po
left join order_items oi on oi.order_id = po.id
order by po.created_at desc, po.id desc, oi.id;

接口还可以使用两条查询：第一条只查 20 个订单，第二条 where order_id in (...) 批量查询明细，再在应用组装。它能避免主表列在每条明细中重复传输，也更适合 ORM 映射。

模式十三：条件放 ON 还是 WHERE

目标：返回全部订单，并关联成功支付金额。

正确：

sql

select o.id,
       p.paid_amount
from orders o
left join payments p
  on p.order_id = o.id
 and p.status = 'SUCCESS';

如果写成：

sql

select o.id,
       p.paid_amount
from orders o
left join payments p on p.order_id = o.id
where p.status = 'SUCCESS';

没有成功支付的订单会被 where 过滤，结果语义接近内连接。

判断规则不是“右表条件永远放 on”，而是：

条件决定哪些右表行可以匹配，同时要保留无匹配左表行：放 on。
条件用于过滤最终结果：放 where。
内连接下优化器常能下推条件，但外连接下位置会改变结果语义。

模式十四：相关子查询与预聚合 Join

题目：每个用户显示已支付金额。

模式十五：不要用 DISTINCT 掩盖错误 Join

下面 SQL 想找买过商品的用户：

sql

select distinct u.id, u.username
from users u
join orders o on o.user_id = u.id
join order_items oi on oi.order_id = o.id;

结果可能正确，但执行过程先展开所有订单明细，再做去重。如果只关心存在性，应使用 exists。

distinct 合理的场景包括业务本身要求对投影列去重；不合理的场景是为了消除错误 Join 或遗漏关联条件造成的重复。

模式十六：避免 OR 连接条件

低效且容易重复的关联：

sql

select *
from users u
join contacts c
  on c.mobile = u.mobile
  or c.email = u.email;

问题：

优化器难以选择单一高效访问路径。
同时手机号和邮箱匹配时可能产生重复或歧义。
NULL 与大小写规则使结果更复杂。

可以根据业务唯一性拆成两个明确分支，再处理优先级和去重：

sql

select u.id, c.id, 'MOBILE' as match_type
from users u
join contacts c on c.mobile = u.mobile
where u.mobile is not null

union all

select u.id, c.id, 'EMAIL' as match_type
from users u
join contacts c on c.email = u.email
where u.email is not null
  and not exists (
    select 1
    from contacts mobile_match
    where mobile_match.mobile = u.mobile
      and u.mobile is not null
  );

真实方案取决于“手机号优先还是邮箱优先”“是否允许一个标识关联多个联系人”，不能只做语法替换。

模式十七：类型和 Collation 必须一致

危险关联：

sql

select *
from orders o
join external_orders e
  on e.order_no = o.id;

如果 e.order_no 是 varchar、o.id 是 bigint，会发生隐式转换，可能导致错误匹配或索引利用异常。

字符串 Join 还要检查：

字符集。
collation。
是否区分大小写。
尾随空格。
字段长度和规范化规则。

正确方向是统一模型类型。临时 cast 或 collate 可以救急，但函数作用于大表 Join Key 会增加计算并可能影响索引。

模式十八：复杂关联中的锁

普通一致性读主要通过 MVCC，不会因为 Join 多就自动锁住所有表。但以下语句属于当前读或写操作：

sql

select o.id, p.id
from orders o
join payments p on p.order_id = o.id
where o.id = ?
for update;

锁范围取决于：

实际访问的索引记录。
执行计划与 Join 顺序。
条件是唯一等值还是范围。
事务隔离级别。
是否访问并锁定两侧记录。

多表更新和加锁查询应：

保持不同业务路径的表和记录访问顺序一致。
让条件命中索引。
缩短事务。
避免在事务中调用远程服务。
用死锁日志和 Performance Schema 还原等待关系。

执行计划怎么看复杂 Join

sql

explain analyze
select ...;

重点不是只看有没有 key：

观察项	要回答的问题
Join 顺序	哪张表先产生结果，为什么
估算行数	优化器认为每一步有多少行
实际行数	是否存在严重估算偏差
loops	被驱动访问重复了多少次
rows × loops	总处理规模是否被循环放大
Filter	条件是否过晚过滤大量数据
Sort/Temporary	排序聚合是否落在大结果集上
执行时间	真正耗时集中在哪个节点

例如被驱动表单次点查只需 0.1ms，但执行 100 万次，仍然可能耗时巨大。

复杂 Join 的索引设计

等值关联

被驱动表 Join Key 通常需要索引：

sql

payments(order_id)
order_items(order_id)
refunds(payment_id)
employees(manager_id)

关联加过滤

索引应匹配实际访问：

sql

payments(order_id, status, paid_at, id)

适合按订单关联后筛选支付状态，并进一步按时间访问。顺序不能只按字段“重要性”，要结合等值、范围和排序。

外层过滤

如果订单先按状态和时间缩小：

sql

orders(status, created_at, id)

先将驱动结果从千万级缩小到几千行，再关联支付和明细，通常比只优化被驱动表更有效。

预聚合

预聚合子查询要能按分组键访问：

sql

order_items(order_id)
payments(order_id, status)

但如果预聚合仍扫描全历史数据，应把订单时间或目标主键范围传入聚合分支，而不是聚合整库后再过滤少量订单。

复杂关联查询练习

练习 1：订单完整财务视图

要求一行一个订单，返回用户、订单金额、明细金额、成功支付金额、成功退款金额和净支付金额。不能出现一对多放大。

参考答案

sql

with item_summary as (
  select order_id,
         sum(quantity * unit_price) as item_amount
  from order_items
  group by order_id
), payment_summary as (
  select order_id,
         sum(paid_amount) as paid_amount
  from payments
  where status = 'SUCCESS'
  group by order_id
), refund_summary as (
  select p.order_id,
         sum(r.refund_amount) as refund_amount
  from payments p
  join refunds r on r.payment_id = p.id
  where r.status = 'SUCCESS'
  group by p.order_id
)
select o.id,
       u.username,
       o.total_amount,
       coalesce(i.item_amount, 0) as item_amount,
       coalesce(p.paid_amount, 0) as paid_amount,
       coalesce(r.refund_amount, 0) as refund_amount,
       coalesce(p.paid_amount, 0) - coalesce(r.refund_amount, 0)
         as net_paid_amount
from orders o
join users u on u.id = o.user_id
left join item_summary i on i.order_id = o.id
left join payment_summary p on p.order_id = o.id
left join refund_summary r on r.order_id = o.id;

练习 2：支付成功但没有物流记录的订单

参考答案

sql

select o.id, o.user_id, o.created_at
from orders o
where exists (
  select 1
  from payments p
  where p.order_id = o.id
    and p.status = 'SUCCESS'
)
and not exists (
  select 1
  from shipments s
  where s.order_id = o.id
);

只判断存在和不存在，不需要展开支付或物流明细，所以用两个半/反连接表达最直接。

练习 3：用户重复购买同一商品

查询用户在至少两个不同已支付订单中购买过的同一商品。

参考答案

sql

select o.user_id,
       oi.product_id,
       count(distinct o.id) as order_count
from orders o
join order_items oi on oi.order_id = o.id
where o.status = 'PAID'
group by o.user_id, oi.product_id
having count(distinct o.id) >= 2;

使用不同订单数，而不是明细行数；同一订单里商品被拆成多条明细时不应误判为复购。

练习 4：经常一起购买的商品对

统计同一已支付订单中共同出现的商品组合，商品对不区分顺序。

参考答案

sql

with distinct_items as (
  select distinct oi.order_id, oi.product_id
  from order_items oi
  join orders o on o.id = oi.order_id
  where o.status = 'PAID'
)
select a.product_id as product_a,
       b.product_id as product_b,
       count(*) as together_order_count
from distinct_items a
join distinct_items b
  on b.order_id = a.order_id
 and b.product_id > a.product_id
group by a.product_id, b.product_id
order by together_order_count desc, product_a, product_b;

b.product_id > a.product_id 同时避免自身配对和 (A,B)/(B,A) 重复。同一订单同一商品先去重，避免重复明细放大组合次数。

练习 5：订单创建时目录价格与成交价不一致

参考答案

sql

select o.id as order_id,
       oi.product_id,
       oi.unit_price,
       ph.price as catalog_price
from orders o
join order_items oi on oi.order_id = o.id
join product_price_history ph
  on ph.product_id = oi.product_id
 and o.created_at >= ph.valid_from
 and (o.created_at < ph.valid_to or ph.valid_to is null)
where oi.unit_price <> ph.price;

真实业务中成交价可能受到优惠，价格不一致不一定是异常；必须先明确对比的是目录价、活动价还是最终分摊价。

练习 6：同一用户十分钟内的疑似重复订单

订单金额相同且与上一单间隔不超过十分钟。

参考答案

sql

with ordered as (
  select o.*,
         lag(id) over(
           partition by user_id, total_amount
           order by created_at, id
         ) as previous_order_id,
         lag(created_at) over(
           partition by user_id, total_amount
           order by created_at, id
         ) as previous_created_at
  from orders o
)
select id,
       user_id,
       total_amount,
       created_at,
       previous_order_id,
       previous_created_at
from ordered
where previous_created_at is not null
  and created_at <= previous_created_at + interval 10 minute;

这只是疑似规则。生产还可能比较商品集合、收货地址、支付渠道和幂等业务号。

练习 7：某时点订单状态

状态事件表记录完整历史，查询每个订单在参数时间 ? 时最后一个已发生状态。

参考答案

sql

with ranked as (
  select e.*,
         row_number() over(
           partition by order_id
           order by event_time desc, id desc
         ) as rn
  from order_status_events e
  where event_time <= ?
)
select o.id,
       r.status,
       r.event_time
from orders o
left join ranked r
  on r.order_id = o.id
 and r.rn = 1;

先在 CTE 中限制目标时点，再取每个订单最后一条。索引 (order_id, event_time desc, id desc) 有利于按订单查询历史，但全量时点快照仍可能处理大量事件，应考虑快照表或分批计算。

练习 8：每个城市销量最高的商品

销量相同全部返回。

参考答案

sql

with city_product_sales as (
  select u.city,
         oi.product_id,
         sum(oi.quantity) as sold_quantity
  from users u
  join orders o on o.user_id = u.id
  join order_items oi on oi.order_id = o.id
  where o.status = 'PAID'
  group by u.city, oi.product_id
), ranked as (
  select cps.*,
         dense_rank() over(
           partition by city
           order by sold_quantity desc
         ) as sales_rank
  from city_product_sales cps
)
select r.city,
       p.id as product_id,
       p.product_name,
       r.sold_quantity
from ranked r
join products p on p.id = r.product_id
where r.sales_rank = 1
order by r.city, p.id;

关联链较长，但每一步的粒度很清晰：先形成城市商品销量，再排名，最后补商品名称。

练习 9：内部订单与外部结算差异

要求返回内部缺失、外部缺失和金额不一致三类数据。MySQL 使用全外连接模拟。

参考答案

sql

select o.id as order_id,
       s.order_id as settlement_order_id,
       o.total_amount,
       s.settlement_amount,
       case
         when s.order_id is null then 'EXTERNAL_MISSING'
         when o.total_amount <> s.settlement_amount then 'AMOUNT_MISMATCH'
       end as difference_type
from orders o
left join external_settlements s on s.order_id = o.id
where s.order_id is null
   or o.total_amount <> s.settlement_amount

union all

select null as order_id,
       s.order_id as settlement_order_id,
       null as total_amount,
       s.settlement_amount,
       'INTERNAL_MISSING' as difference_type
from external_settlements s
left join orders o on o.id = s.order_id
where o.id is null;

如果外部表同一订单可能多条结算记录，应先聚合或确定唯一键，否则核对结果也会被放大。

练习 10：分页订单并返回汇总而非明细展开

返回 20 个订单，同时带明细数量、成功支付金额、最新物流状态。

参考答案

sql

with page_orders as (
  select id, user_id, status, total_amount, created_at
  from orders
  where created_at < ?
     or (created_at = ? and id < ?)
  order by created_at desc, id desc
  limit 20
), item_summary as (
  select oi.order_id,
         count(*) as item_line_count,
         sum(oi.quantity) as item_quantity
  from order_items oi
  join page_orders po on po.id = oi.order_id
  group by oi.order_id
), payment_summary as (
  select p.order_id,
         sum(p.paid_amount) as paid_amount
  from payments p
  join page_orders po on po.id = p.order_id
  where p.status = 'SUCCESS'
  group by p.order_id
), shipment_ranked as (
  select s.order_id,
         s.status,
         row_number() over(
           partition by s.order_id
           order by s.updated_at desc, s.id desc
         ) as rn
  from shipments s
  join page_orders po on po.id = s.order_id
)
select po.*,
       coalesce(i.item_line_count, 0) as item_line_count,
       coalesce(i.item_quantity, 0) as item_quantity,
       coalesce(p.paid_amount, 0) as paid_amount,
       s.status as latest_shipment_status
from page_orders po
left join item_summary i on i.order_id = po.id
left join payment_summary p on p.order_id = po.id
left join shipment_ranked s
  on s.order_id = po.id
 and s.rn = 1
order by po.created_at desc, po.id desc;

各聚合分支都只处理当前页的 20 个订单，避免对全历史明细预聚合。是否重复引用 CTE、是否物化以及具体执行方式要在目标版本查看执行计划。

面试追问

为什么多表 Join 后金额会翻倍

因为订单明细、支付、退款都是订单的一对多关系，同时 Join 会形成组合行。例如 3 条明细乘 2 条支付乘 2 条退款得到 12 行，每个业务金额在组合中重复出现。正确做法是分别按订单预聚合到一行，再进行一对一 Join；sum(distinct amount) 会错误合并金额相同但业务上不同的记录。

EXISTS 和 JOIN 怎么选

只判断关联记录是否存在时优先表达为 exists，它不会把右表多行展开到结果；需要返回右表字段时使用 Join；需要聚合右表时先明确粒度，必要时预聚合。现代优化器可能在半连接、Join 和子查询之间改写，性能仍要看计划。

复杂 Join 怎么优化

先确认结果粒度和关联基数，排除错误笛卡尔放大；尽早用业务条件缩小驱动结果；让被驱动表 Join Key 有合适索引；多个一对多先分别预聚合；分页先查主表；只判断存在使用半连接；再用 EXPLAIN ANALYZE 检查每一步实际行数、loops、排序和过滤，不能只看有没有索引。

为什么 Join 后加 DISTINCT 不是通用解决方案

distinct 只能消除投影结果完全相同的行，会增加排序或去重成本，而且可能掩盖遗漏 Join 条件、错误一对多展开和结果粒度不清。金额聚合已经被放大后再 distinct 也无法恢复正确语义。

先聚合再 Join 一定更快吗

不一定。它能稳定粒度并避免多条一对多相乘，但如果只查少量订单，却先聚合整张历史明细表，成本可能更高。应把目标订单范围传入聚合分支，或者先分页/过滤主表，再关联聚合，并用执行计划验证。

复杂关联查询检查清单

最终一行代表什么。
每条关系是一对一、一对多还是多对多。
Join 后预计行数如何变化。
是否同时 Join 了多个一对多分支。
是否错误使用 sum(distinct ...) 或 distinct 修补重复。
只判断存在时能否使用 exists。
需要最新一条时并列规则是否稳定。
外连接右表条件是否错误放进 where。
时间区间是否使用半开边界，历史区间是否重叠。
分页限制的是父对象还是 Join 后明细行。
Join Key 类型、字符集和 collation 是否一致。
驱动表能否先被高选择性条件缩小。
被驱动表关联列是否有索引。
rows × loops 是否形成巨大处理量。
复杂聚合是否可以按当前目标主键集合缩小。
加锁查询会访问和锁定哪些索引范围。

关联知识点

查询与 Join：Nested Loop、BKA、MRR、Hash Join 和驱动表。
SQL 查询面试题：基础、聚合、窗口和连续行为题。
SQL 进阶：CTE、子查询、窗口函数和 NULL。
MySQL 索引：联合索引、覆盖索引和最左前缀。
EXPLAIN：Join 顺序、扫描行数、循环和真实耗时。
MySQL 锁：当前读、多表访问和死锁。
分库分表：跨分片 Join 与全局归并的额外限制。

本章小结

复杂关联查询的核心不是 Join 表越多越高级，而是始终保持结果粒度正确。多个一对多分支要警惕乘法放大；存在性使用半连接；最新一条使用稳定排序；时态关联使用明确的半开有效区间；父子列表先分页父表；金额和数量分别在正确粒度聚合。性能优化则回到驱动结果规模、被驱动索引、实际行数和 loops，用执行计划证明，而不是靠 distinct、force index 或盲目拆 SQL 猜测。

JDK 8/11/17/21 LTS演进

线程池

MySQL 复杂关联查询实战 ​

复杂 Join 的第一原则：先确定结果粒度 ​

关联基数决定结果行数 ​

扩展表结构 ​

扩展表关系速查 ​

扩展表字段中文速查 ​

支付和退款 ​

物流和商品价格历史 ​

状态事件和外部结算 ​

模式一：多个一对多 Join 导致金额翻倍 ​

错误写法 ​

正确写法：各自预聚合后再关联 ​

模式二：只判断是否存在时使用半连接 ​

容易产生重复的写法 ​

推荐 EXISTS ​

模式三：查询不存在关联记录使用反连接 ​

模式四：每组最新一条关联记录 ​

MySQL 8 窗口函数 ​

MySQL 5.7 反连接写法 ​

模式五：关联后取每组 Top N ​

模式六：多对多关联 ​

模式七：关系除法——全部满足 ​

计数法 ​

双重 NOT EXISTS ​

模式八：时态关联——查询当时有效的记录 ​

模式九：区间重叠关联 ​

模式十：自关联与递归关联 ​

只查直接经理 ​

查询全部上级链路 ​

模式十一：模拟 FULL OUTER JOIN ​

模式十二：父表分页后再关联明细 ​

错误写法 ​

先分页订单，再关联明细 ​

模式十三：条件放 ON 还是 WHERE ​

模式十四：相关子查询与预聚合 Join ​

模式十五：不要用 DISTINCT 掩盖错误 Join ​

模式十六：避免 OR 连接条件 ​

模式十七：类型和 Collation 必须一致 ​

模式十八：复杂关联中的锁 ​

执行计划怎么看复杂 Join ​

复杂 Join 的索引设计 ​

等值关联 ​

关联加过滤 ​

外层过滤 ​

预聚合 ​

复杂关联查询练习 ​

练习 1：订单完整财务视图 ​

练习 2：支付成功但没有物流记录的订单 ​

练习 3：用户重复购买同一商品 ​

练习 4：经常一起购买的商品对 ​

练习 5：订单创建时目录价格与成交价不一致 ​

练习 6：同一用户十分钟内的疑似重复订单 ​

练习 7：某时点订单状态 ​

练习 8：每个城市销量最高的商品 ​

练习 9：内部订单与外部结算差异 ​

练习 10：分页订单并返回汇总而非明细展开 ​

面试追问 ​

为什么多表 Join 后金额会翻倍 ​

EXISTS 和 JOIN 怎么选 ​

复杂 Join 怎么优化 ​

为什么 Join 后加 DISTINCT 不是通用解决方案 ​

先聚合再 Join 一定更快吗 ​

复杂关联查询检查清单 ​

关联知识点 ​

本章小结 ​

MySQL 复杂关联查询实战

复杂 Join 的第一原则：先确定结果粒度

关联基数决定结果行数

扩展表结构

扩展表关系速查

扩展表字段中文速查

支付和退款

物流和商品价格历史

状态事件和外部结算

模式一：多个一对多 Join 导致金额翻倍

错误写法

正确写法：各自预聚合后再关联

模式二：只判断是否存在时使用半连接

容易产生重复的写法

推荐 EXISTS

模式三：查询不存在关联记录使用反连接

模式四：每组最新一条关联记录

MySQL 8 窗口函数

MySQL 5.7 反连接写法

模式五：关联后取每组 Top N

模式六：多对多关联

模式七：关系除法——全部满足

计数法

双重 NOT EXISTS

模式八：时态关联——查询当时有效的记录

模式九：区间重叠关联

模式十：自关联与递归关联

只查直接经理

查询全部上级链路

模式十一：模拟 FULL OUTER JOIN

模式十二：父表分页后再关联明细

错误写法

先分页订单，再关联明细

模式十三：条件放 ON 还是 WHERE

模式十四：相关子查询与预聚合 Join

模式十五：不要用 DISTINCT 掩盖错误 Join

模式十六：避免 OR 连接条件

模式十七：类型和 Collation 必须一致

模式十八：复杂关联中的锁

执行计划怎么看复杂 Join

复杂 Join 的索引设计

等值关联

关联加过滤

外层过滤

预聚合

复杂关联查询练习

练习 1：订单完整财务视图

练习 2：支付成功但没有物流记录的订单

练习 3：用户重复购买同一商品

练习 4：经常一起购买的商品对

练习 5：订单创建时目录价格与成交价不一致

练习 6：同一用户十分钟内的疑似重复订单

练习 7：某时点订单状态

练习 8：每个城市销量最高的商品

练习 9：内部订单与外部结算差异

练习 10：分页订单并返回汇总而非明细展开

面试追问

为什么多表 Join 后金额会翻倍

EXISTS 和 JOIN 怎么选

复杂 Join 怎么优化

为什么 Join 后加 DISTINCT 不是通用解决方案

先聚合再 Join 一定更快吗

复杂关联查询检查清单

关联知识点

本章小结