mysql - 从加入mysql获取矩阵表

Question

我有多个结构如下的表。

主表

CREATE TABLE `master` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `code` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `code` (`code`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

id  code
1   100050
2   100051
3   100052

第一个映射表

CREATE TABLE `mappings_one` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `master_code` int(11) NOT NULL,
  `mappings_one_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `master_code` (`master_code`),
  KEY `mappings_one_code` (`mappings_one_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

id  master_code mappings_one_code
1   100050      2346
2   100051      1267
3   100051      3890
4   100052      5698

第二个映射表

CREATE TABLE `mappings_two` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `master_code` int(11) NOT NULL,
  `mappings_two_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `master_code` (`master_code`),
  KEY `mappings_two_code` (`mappings_two_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

id  master_code mappings_two_code
1   100050      BE45
2   100050      HO87
3   100051      YT67
4   100051      AX56

还有很多映射表通过 master_code 字段与主表相关联。我想找到如下结果:

id  master_code mappings_one_code   mappings_two_code
1   100050      2346                BE45
2   100050      null                HO87
3   100051      1267                YT67
4   100051      3890                AX56
5   100052      5698                null

我尝试了以下查询，但无法获得上述所需的结果。谁能指导我解决同样的问题？

SELECT 
  m.`code`,m.`name`,
  m1.`mappings_one_code` AS 'mappings_one_code',
  m2.`mappings_two_code` AS 'mappings_two_code'
FROM 
  master m LEFT JOIN 
  mappings_one m1 ON m1.`master_code` = m.`code` LEFT JOIN 
  mappings_two m2 ON m2.`master_code` = m.`code`

Answer 1

不幸的是 mysql 不支持 FULL OUTER JOIN 所以你需要这样的东西:

http://sqlfiddle.com/#!9/127f4/1

SELECT m3.code, m2.u_idx, m3.name, m3.mappings_one_code, m2.mappings_two_code 
FROM (SELECT 
  m.`code`,
  IF(@idx IS NULL, @idx:=1, IF(@code = code, @idx:=@idx+1, @idx:=1)),
  IF(@code IS NULL, @code:= code,IF(@code = code, @code,   @code:= code)),
  CONCAT(@code,'-',@idx) u_idx,
  m.`name`,
  m1.`mappings_one_code` AS 'mappings_one_code'
FROM `master` m 
LEFT JOIN 
  mappings_one m1 
ON m1.`master_code` = m.`code` 
) m3
LEFT JOIN 
  (SELECT 
  m.`code`,
  IF(@idx IS NULL, @idx:=1, IF(@code = code, @idx:=@idx+1, @idx:=1)),
  IF(@code IS NULL, @code:= code,IF(@code = code, @code,   @code:= code)),
  CONCAT(@code,'-',@idx) u_idx,
  m.`name`,
  m1.`mappings_two_code` AS 'mappings_two_code'
FROM `master` m 
LEFT JOIN 
  mappings_two m1 
ON m1.`master_code` = m.`code` 
) m2
on m3.u_idx = m2.u_idx
UNION
SELECT m2.code, m2.u_idx, m2.name, m3.mappings_one_code, m2.mappings_two_code 
FROM (SELECT 
  m.`code`,
  IF(@idx IS NULL, @idx:=1, IF(@code = code, @idx:=@idx+1, @idx:=1)),
  IF(@code IS NULL, @code:= code,IF(@code = code, @code,   @code:= code)),
  CONCAT(@code,'-',@idx) u_idx,
  m.`name`,
  m1.`mappings_one_code` AS 'mappings_one_code'
FROM `master` m 
LEFT JOIN 
  mappings_one m1 
ON m1.`master_code` = m.`code` 
) m3
RIGHT JOIN 
  (SELECT 
  m.`code`,
  IF(@idx IS NULL, @idx:=1, IF(@code = code, @idx:=@idx+1, @idx:=1)),
  IF(@code IS NULL, @code:= code,IF(@code = code, @code,   @code:= code)),
  CONCAT(@code,'-',@idx) u_idx,
  m.`name`,
  m1.`mappings_two_code` AS 'mappings_two_code'
FROM `master` m 
LEFT JOIN 
  mappings_two m1 
ON m1.`master_code` = m.`code` 
) m2
on m3.u_idx = m2.u_idx

ORDER BY u_idx