如何用LEFT JOIN获取每个活跃公司的最新邮件？需先ORDER BY再GROUP BY

我明白你的问题所在了——你原SQL里的ORDER BY是在GROUP BY之后执行的，这就导致分组的时候根本没拿到每个公司的最新邮件，最后排序只是把那些随机选出的结果排了个序，完全不是你想要的效果。要实现“先按邮件日期降序筛选最新记录，再分组”的逻辑，我们可以用两种常见的方案来解决：

方案一：子查询获取每个公司的最新邮件日期（兼容多数数据库） #

先通过子查询找出每个公司对应的最新邮件日期，再通过这个日期关联回邮件表拿到完整的邮件信息，最后和公司表关联筛选活跃状态的公司：

SELECT c.*, e.*
FROM companies c
LEFT JOIN (
    -- 先获取每个公司的最新邮件日期
    SELECT company_id, MAX(date) AS latest_date
    FROM emails
    GROUP BY company_id
) latest_e ON c.company_id = latest_e.company_id
-- 通过公司ID+最新日期关联，拿到对应的最新邮件
LEFT JOIN emails e ON latest_e.company_id = e.company_id AND latest_e.latest_date = e.date
WHERE c.status = 'active';

方案二：使用窗口函数（适合支持窗口函数的数据库，如MySQL 8+、PostgreSQL、SQL Server等） #

这种方式更直观，利用ROW_NUMBER()窗口函数给每个公司的邮件按日期降序编号，然后只取编号为1的那条（也就是最新的邮件）：

SELECT *
FROM (
    SELECT 
        c.*,
        e.*,
        -- 按公司分组，组内按邮件日期降序编号
        ROW_NUMBER() OVER (PARTITION BY c.company_id ORDER BY e.date DESC) AS rn
    FROM companies c
    LEFT JOIN emails e ON c.company_id = e.company_id
    WHERE c.status = 'active'
) sub_query
-- 只保留每个公司的第一条（最新）记录
WHERE rn = 1;

如果存在同一公司在同一日期有多封邮件的情况，ROW_NUMBER()会随机选取其中一条；如果想要保留所有同一天的最新邮件，可以把ROW_NUMBER()换成RANK()或者DENSE_RANK()。

内容的提问来源于stack exchange，提问作者Zoltan