左联接内的mysql子查询 [英] mysql subquery inside a LEFT JOIN

查看:179
本文介绍了左联接内的mysql子查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个查询,需要一个名为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子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆