MySQL的加入多个表使用LIMIT最后一个表每结果的DateTime(MySQL JOIN Mul

我有一个有趣的一个。 我有3个表，我的加盟。 我参加的最后一个表，我只想看到日期的最新条目，而不是翻倍了我的结果与相同的用户，只是有不同的登录时间。

实施例（这是一个示例表只）：

    SELECT a.user_id
       a.user_name,
       b.department,
       c.last_logon_date_time,
       c.computer_name
 FROM table1 a
 LEFT OUTER JOIN table2 b ON a.user_id = b.user_id
 LEFT OUTER JOIN table3 c ON c.user_id = c.user_id

下面会给我我寻找，但看起来非常低效的，是很慢的结果。 加快这件事，使之比较有效的方法吗？

我能做到这一点

         SELECT a.user_id
       a.user_name,
       b.department,
       (SELECT c.last_logon_datetime FROM table 3 c WHERE a.user_id = c.user_id ORDER BY c.last_logon_datetime DESC LIMIT 1) as last_logon_datetime,
       (SELECT c.computer_name FROM table 3 c WHERE a.user_id = c.user_id ORDER BY c.last_logon_datetime DESC LIMIT 1) as  computer_name
 FROM table1 a
 LEFT OUTER JOIN table2 b ON a.user_id = b.user_id

谢谢。

Answer 1:

你可以加入从这样的子查询创建一个“表”：

SELECT t1.user_id, t1.user_name, t2.department, t3.computer_name, t3.logon_date
FROM table1 AS t1
LEFT OUTER JOIN table2 AS t2 ON t1.user_id = t2.user_id
LEFT OUTER JOIN
(SELECT user_id, computer_name, MAX(last_logon_date) AS `logon_date` FROM table 3 GROUP BY computer_name) AS t3 ON t1.user_id = t3.user_id

文章来源: MySQL JOIN Multiple Tables With LIMIT Last Table By DateTime per result