MySQL 左连接中的mysql子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12309892/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql subquery inside a LEFT JOIN
提问by coffeemonitor
I have a query that needs the most recent record from a secondary table called tbl_emails_sent.
我有一个查询需要来自名为 的辅助表的最新记录tbl_emails_sent。
That table holds all the emails sent to clients. And most clients have several to hundreds of emails recorded. I want to pull a query that displays the most recent.
该表包含发送给客户的所有电子邮件。大多数客户都记录了几到数百封电子邮件。我想提取一个显示最新的查询。
Example:
例子：
SELECT c.name, c.email, e.datesent
FROM `tbl_customers` c
LEFT JOIN `tbl_emails_sent` e ON c.customerid = e.customerid
I'm guessing a LEFT JOIN with a subquery would be used, but I don't delve into subqueries much. Am I going the right direction?
我猜会使用带有子查询的 LEFT JOIN，但我并没有深入研究子查询。我走对了方向吗？
Currently the query above isn't optimized for specifying the most recent record in the table, so I need a little assistance.
目前，上面的查询并未针对指定表中的最新记录进行优化，因此我需要一些帮助。
回答by John Woo
It should be like this, you need to have a separate query to get the maximum date (or the latest date) that the email was sent.
应该是这样的，你需要有一个单独的查询来获取电子邮件发送的最大日期（或最晚日期）。
SELECT a.*, b.*
FROM tbl_customers a
INNER JOIN tbl_emails_sent b
ON a.customerid = b.customerid
INNER JOIN
(
SELECT customerid, MAX(datesent) maxSent
FROM tbl_emails_sent
GROUP BY customerid
) c ON c.customerid = b.customerid AND
c.maxSent = b.datesent
回答by mrmryb
Would this not work?
这行不通吗？
SELECT t1.datesent,t1.customerid,t2.email,t2.name
FROM
(SELECT max(datesent) AS datesent,customerid
FROM `tbl_emails_sent`
) as t1
INNER JOIN `tbl_customers` as t2
ON t1.customerid=t2.customerid
Only issue you have then is what if two datesents are the same, what is the deciding factor in which one gets picked?
那么你唯一的问题是如果两个日期相同，那么选择一个的决定因素是什么？
