mysql - MySql-从表2获取字段，如果不是，则从表1获取

我有两个表complain和repair。如果ass_to_per中不存在repair的complain，我想从id或complain获得repair [最新的]。

说明：

我想从ass_to_per表以及complain表中获取repair。但是它们之间存在关系，在repair中有一个名为com_id的字段，它是一个外键。因此，我想从ass_to_per获取complain，还要检查repair的外键，如果有的话再检查修复的ass_to_per。如果有的话作为结果。

我有sqlfiddle，用于在线测试：sqlfiddle.com

下表和数据如下。

抱怨

CREATE TABLE `complain` (
  `id` int(11) NOT NULL,
  `ass_to_per` varchar(50) NOT NULL
);
INSERT INTO `complain` (`id`, `ass_to_per`) VALUES
(1, 'frayne'),
(2, 'murad'),
(4, 'frayne'),
(5, 'murad'),
(6, 'frayne'),
(7, 'frayne');

修理

CREATE TABLE `repair` (
  `id` int(11) NOT NULL,
  `com_id` int(11) NOT NULL,
  `ass_to_per` varchar(50) NOT NULL
);
INSERT INTO `repair` (`id`, `com_id`, `ass_to_per`) VALUES
(1, 1, 'frayne'),
(2, 1, 'murad'),
(3, 4, 'frayne'),
(4, 6, 'murad'),
(5, 2, 'murad'),
(6, 5, 'frayne');

我的查询：

SELECT * FROM `complain`
WHERE `id` IN (SELECT DISTINCT(`com_id`) FROM `repair` WHERE `ass_to_per` = 'frayne') OR `ass_to_per`='frayne'

查询结果

id |    ass_to_per
--------------
1  |    frayne
4  |    frayne
5  |    murad
6  |    frayne
7  |    frayne

分析

id | ass_to_per[complain] | ass_to_per[repair]
--------------
1  |    murad             |    frayne
2  |    murad             |    murad
4  |    frayne            |    frayne          //need this one
5  |    murad             |    frayne          //need this one
6  |    frayne            |    murad
7  |    frayne            |                    //need this one

预期结果：

id |    ass_to_per
--------------
4  |    frayne        //ass_to_per from repair
5  |    frayne        //ass_to_per from repair
7  |    frayne        //ass_to_per from complain

最佳答案

COALESCE()将输出找到的第一个非空参数，因此使用联接可能会从抱怨或修复中获取ass_to_per，具体取决于存在的那个参数：

SELECT
    complain.id,
    COALESCE(repair3.ass_to_per, complain.ass_to_per) as ass_to_per
FROM complain
LEFT JOIN
    (SELECT max(id) as maxid, com_id FROM repair GROUP BY com_id) as repair2
    ON complain.id = repair2.com_id
LEFT JOIN repair as repair3
    ON repair2.maxid = repair3.id
GROUP BY complain.id

如果要对已计算的ass_to_per进一步过滤（例如在“ frayne”的示例中），只需将此选择作为子查询嵌入：

SELECT *
FROM (
  SELECT
    complain.id,
    COALESCE(repair3.ass_to_per, complain.ass_to_per) as ass_to_per
  FROM complain
  LEFT JOIN (SELECT max(id) as maxid, com_id FROM repair GROUP BY com_id) AS repair2
    ON complain.id = repair2.com_id
  LEFT JOIN repair as repair3
    ON repair2.maxid = repair3.id
  GROUP BY complain.id
) AS mydata
WHERE mydata.ass_to_per = 'frayne'
ORDER BY mydata.id;

在这里提琴：http://sqlfiddle.com/#!9/33433/49