如何编写MySQL多表关联查询语句，关联主表和事务表得到目标结果

普通的INNER JOIN或LEFT JOIN仅能返回两张表匹配的全量关联行，无法满足按主表维度对事务数据做分组筛选、行列转换等需求，可通过窗口函数+左连接的组合实现目标结果：

实现思路 #

• 用LEFT JOIN关联两张表，保留主表的全部记录，避免匹配不到事务数据时主表行丢失
• 用窗口函数按主表关联键对事务数据做分组排序，可实现取每个主表对应的最新N条事务、事务数据行转列等需求
• 最后根据目标结果的格式要求做过滤、聚合操作即可

示例代码 #

预设表结构（可根据你的实际字段调整） #

• 主表master：主键master_id，其他基础属性字段如master_name等
• 事务表transaction：主键trans_id，外键master_id关联主表，其他事务字段如trans_amount（交易金额）、trans_time（交易时间）等

场景1：返回每个主表记录对应的最新3条事务，无事务的对应字段补空 #

（支持MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库）

WITH ranked_trans AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY master_id ORDER BY trans_time DESC) AS trans_rn
  FROM transaction
)
SELECT 
  m.*,
  rt.trans_id,
  rt.trans_amount,
  rt.trans_time
FROM master m
LEFT JOIN ranked_trans rt 
  ON m.master_id = rt.master_id
  AND rt.trans_rn <= 3
ORDER BY m.master_id, rt.trans_rn;

场景2：返回每个主表记录对应最近3次交易的金额作为单独列（行转列格式） #

WITH ranked_trans AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY master_id ORDER BY trans_time DESC) AS trans_rn
  FROM transaction
)
SELECT 
  m.master_id,
  m.master_name,
  MAX(CASE WHEN trans_rn = 1 THEN trans_amount END) AS latest_trans_amount_1,
  MAX(CASE WHEN trans_rn = 2 THEN trans_amount END) AS latest_trans_amount_2,
  MAX(CASE WHEN trans_rn = 3 THEN trans_amount END) AS latest_trans_amount_3
FROM master m
LEFT JOIN ranked_trans rt 
  ON m.master_id = rt.master_id
GROUP BY m.master_id, m.master_name
ORDER BY m.master_id;

低版本MySQL（不支持窗口函数）适配方案 #

SELECT 
  m.*,
  t1.trans_id,
  t1.trans_amount,
  t1.trans_time
FROM master m
LEFT JOIN transaction t1 
  ON m.master_id = t1.master_id
  AND (
    SELECT COUNT(*) 
    FROM transaction t2 
    WHERE t2.master_id = t1.master_id AND t2.trans_time >= t1.trans_time
  ) <= 3
ORDER BY m.master_id, t1.trans_time DESC;

内容的提问来源于stack exchange，提问作者Sony Elgadri