mysql - MySQL从3个表中创建具有最新ID的 View

我用不同的方法尝试了几次，但没有像应该的那样工作：

-主表（m1）

+-----+-----------+
| Cid | location  |
+-----+-----------+
|  1  | Amsterdam |
|  2  | Berlin    |
|  3  | Paris     |

-信息表1（i1）

+-----+-----+-------+------------------+
| id  | Cid | light |       date       |
+-----+-----+-------+------------------+
| 995 |   1 | off   | 10:30 22-09-2017 |
| 994 |   3 | on    | 10:30 22-09-2017 |
| 993 |   2 | off   | 10:30 22-09-2017 |
| 992 |   1 | on    | 09:20 22-09-2017 |
| 991 |   2 | on    | 09:20 22-09-2017 |

-信息表2（i2）

+-----+-----+---------+
| id  | Cid |  task   |
+-----+-----+---------+
| 335 |   3 | measure |
| 334 |   2 | reboot  |
| 333 |   2 | standby |
| 332 |   1 | fixture |
| 331 |   2 | measure |

-我想要这样输出（它包含结果中链接到Cid的最新ID）：

+-------------+-----------+----------+------------------+---------+
| Cid & m1 id | location  | i1 light |     i1 date      | i2 task |
+-------------+-----------+----------+------------------+---------+
|           1 | Amsterdam | off      | 10:30 22-09-2017 | fixture |
|           2 | Berlin    | off      | 10:30 22-09-2017 | reboot  |
|           3 | Paris     | on       | 10:30 22-09-2017 | measure |

我尝试过的是以下内容；

SELECT DISTINCT
        `m1`.`id`,
        `m1`.`location`,
        `i1`.`light`,
        `i1`,`date`,
        `i2`.`task`,
    FROM
        ((`m1`
        JOIN `i1` ON ((`i1`.`Cid` = `m1`.`id`)))
        JOIN `i2` ON ((`i2`.`Cid` = `m1`.`id`)))
    WHERE
        `i1`.`id` IN (SELECT
                MAX(`i1`.`id`)
            FROM
                `i1`
            GROUP BY `i1`.`Cid`)
    ORDER BY `m1`.`id`

这只会导致没有双打的i1，但会给出更多的结果，因为有更多具有相同Cid的i2行。我也尝试过左联接，但没有成功。

非常感激！

最佳答案

我知道可能有更好的方法可以做到这一点，但这应该可行。

SELECT m1.id,
       m1.location,
       i1_max.light,
       i1_max.date,
       i2_max.task
FROM   m1
       JOIN (SELECT id,
                    Cid,
                    light,
                    date
             FROM   i1
             WHERE  i1.Cid = m1.id
             ORDER  BY date DESC
             LIMIT  1) AS i1_max
         ON m1.id = i1_max.Cid
       JOIN (SELECT id,
                    Cid,
                    task
             FROM   i2
             WHERE  i2.Cid = m1.id
             ORDER  BY id DESC
             LIMIT  1) AS i2_max
         ON m1.id = i2_max.Cid

关于mysql - MySQL从3个表中创建具有最新ID的 View ，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42914875/