MySQL联接每个表,每个结果带有LIMIT最后一个表,按DateTime [英] MySQL JOIN Multiple Tables With LIMIT Last Table By DateTime per result
本文介绍了MySQL联接每个表,每个结果带有LIMIT最后一个表,按DateTime的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个有趣的.我要加入3张桌子.我加入的最后一张表,我只想按日期查看最新的条目,而不想将相同用户的结果加倍,登录时间也不同.
I got an interesting one. I have 3 tables I am joining. The last table I joined, I want to only see the latest entry by date and not double up my results with the same user and just have different login times.
示例(这仅是示例表):
Example (this is a sample table only):
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
以下内容将为我提供所需的结果,但是效率很低而且非常慢.有什么方法可以加快速度并提高效率吗?
Below will give me the results I am looking for but looks very inefficient and is really slow. Any way of speeding this up and making it more efficent?
我可以做到
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
谢谢.
推荐答案
您可以联接到从子选择创建的表"中,如下所示:
You can JOIN to a "table" created from a subselect like this:
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联接每个表,每个结果带有LIMIT最后一个表,按DateTime的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文