问题描述
我有一个查询需要来自名为 tbl_emails_sent 的辅助表中的最新记录.
I have a query that needs the most recent record from a secondary table called 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.
例子:
SELECT c.name, c.email, e.datesent
FROM `tbl_customers` c
LEFT JOIN `tbl_emails_sent` e ON c.customerid = e.customerid
我猜会使用带有子查询的 LEFT JOIN，但我并没有深入研究子查询.我的方向正确吗?
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?
目前，上述查询并未针对指定表中的最新记录进行优化，因此我需要一些帮助.
Currently the query above isn't optimized for specifying the most recent record in the table, so I need a little assistance.
推荐答案
应该是这样，需要单独查询才能获取邮件发送的最大日期(或最晚日期).
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
这篇关于左连接中的mysql子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！