MySQL5.6环境下不使用CTE、Row_number实现多表关联合并为单表

问题原因 #

你原来的SQL出现重复行的核心原因是同一个ID在B、C、D三个业务表中存储的行数不一致，直接仅按ID关联会产生笛卡尔积。比如ID=1在表B有1行、表C有2行、表D有1行，直接join后就会生成1*2*1=2行重复的B、D字段数据。

解决方案 #

MySQL 5.6 虽然不支持窗口函数，但可以通过用户自定义变量模拟行号功能，给每个ID下的每一行分配一个独立的序号，关联时同时匹配ID和序号即可避免笛卡尔积，符合预期输出。

完整调整后的SQL如下：

SELECT 
  a.id,
  b.name,
  b.state,
  c.subject,
  c.marks,
  d.sports,
  d.matches
FROM table_a a
-- 给表B同ID下的行生成序号
LEFT JOIN (
  SELECT 
    id, name, state,
    @rn_b := IF(@pre_b = id, @rn_b + 1, 1) AS rn,
    @pre_b := id
  FROM table_b, (SELECT @rn_b := 0, @pre_b := NULL) AS init
  ORDER BY id
) b ON a.id = b.id
-- 给表C同ID下的行生成序号，关联时同时匹配ID和行号
LEFT JOIN (
  SELECT 
    id, subject, marks,
    @rn_c := IF(@pre_c = id, @rn_c + 1, 1) AS rn,
    @pre_c := id
  FROM table_c, (SELECT @rn_c := 0, @pre_c := NULL) AS init
  ORDER BY id
) c ON a.id = c.id AND b.rn = c.rn
-- 给表D同ID下的行生成序号，关联时同时匹配ID和行号
LEFT JOIN (
  SELECT 
    id, sports, matches,
    @rn_d := IF(@pre_d = id, @rn_d + 1, 1) AS rn,
    @pre_d := id
  FROM table_d, (SELECT @rn_d := 0, @pre_d := NULL) AS init
  ORDER BY id
) d ON a.id = d.id AND COALESCE(b.rn, c.rn) = d.rn

补充说明 #

如果需要调整同一个ID下数据的展示顺序，可以修改各子查询中ORDER BY后的排序字段，比如要按科目分数倒序排列，就可以将表C子查询的排序规则改为ORDER BY id, marks DESC。

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