mysql - 修改 MYSQL 查询以获得两个表之间的 JOIN

Question

为了不仅从 db_events.events 字段获取结果，而是将其与一些 db_system.devices 字段结合

SELECT e.* 
FROM db_events.events e 
   JOIN (
      SELECT Max(id) MaxId, device_id
      FROM db_events.events
      GROUP BY device_id ) e2 on e.Id = e2.MaxId AND e.device_id = e2.device_id
WHERE e.device_id IN (
            SELECT device_id 
            FROM db_system.devices 
            WHERE vendor = 1)
ORDER BY e.id DESC

如何在不重复子查询的情况下得到它:

            SELECT * 
            FROM db_system.devices 
            WHERE vendor = 1

我需要将 db_system.devices.brand 和 db_system.devices.model 与最终结果结合起来，并且我试着一步一步地修改它，我试过用临时表，我怀疑它应该是简单的东西，但我一直没能做到，当然非常感谢...

Answer 1

这是你想要的吗？

SELECT  e.*, a.* 
FROM    db_events.events e 
        INNER JOIN 
        (
            SELECT Max(id) MaxId, device_id
            FROM db_events.events
            GROUP BY device_id 
        ) e2 on e.Id = e2.MaxId AND 
                e.device_id = e2.device_id
        INNER JOIN db_system.devices a
            ON e.device_id = a.device_id  AND 
                a.vendor = 1
ORDER   BY e.id DESC

条件 a.vendor = 1 也可以移到 WHERE 子句上，结果仍然相同，因为您使用的是 INNER JOIN

SELECT ....
FROM   .... JOIN ....
WHERE  a.vendor = 1
ORDER  BY ...