MySql 从 3 个具有最新 ID 的表创建 View

Question

我尝试了几次不同的方法，但没有得到应有的效果:

-- 主表(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`

这仅产生没有 double 的 i1，但确实给出了更多结果，因为有更多 i2 行具有相同的 Cid。我也尝试过左连接，但没有成功。

非常感谢!

Answer 1

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

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