MySQL5.7下将fundamentals表最新记录关联到companies表的实现问询

适配MySQL 5.7版本的实现方案 #

方案1：关联子查询匹配最新时间 #

直接通过子查询取对应公司的fundamentals最新created_at做关联条件：

SELECT 
    c.*, f.*
FROM companies c
LEFT JOIN fundamentals f 
    ON f.companies_id = c.id
    AND f.created_at = (
        SELECT MAX(created_at) 
        FROM fundamentals 
        WHERE companies_id = c.id
    );

如果存在同一个公司有多条完全相同created_at的记录，上述语句会返回所有符合时间条件的记录。如果仅需要返回1条，可以增加id兜底判断：

SELECT 
    c.*, f.*
FROM companies c
LEFT JOIN fundamentals f 
    ON f.companies_id = c.id
    AND f.id = (
        SELECT MAX(id)
        FROM fundamentals 
        WHERE companies_id = c.id
        AND created_at = (SELECT MAX(created_at) FROM fundamentals WHERE companies_id = c.id)
    );

方案2：预聚合最新时间再关联 #

先通过子查询聚合出每个公司的最新时间，再二次关联fundamentals表取完整数据：

SELECT c.*, f.*
FROM companies c
LEFT JOIN (
    SELECT companies_id, MAX(created_at) AS latest_created
    FROM fundamentals
    GROUP BY companies_id
) AS latest_f 
    ON latest_f.companies_id = c.id
LEFT JOIN fundamentals f
    ON f.companies_id = c.id
    AND f.created_at = latest_f.latest_created;

MySQL 8.0+ 可选优化方案（窗口函数） #

如果后续升级到MySQL 8.0及以上版本，可以使用ROW_NUMBER()窗口函数实现，写法更简洁，性能也更稳定：

WITH ranked_fundamentals AS (
    SELECT 
        *,
        -- 同公司下按创建时间倒序、id倒序排序，最新记录排名为1
        ROW_NUMBER() OVER (PARTITION BY companies_id ORDER BY created_at DESC, id DESC) AS rn
    FROM fundamentals
)
SELECT c.*, rf.*
FROM companies c
LEFT JOIN ranked_fundamentals rf
    ON rf.companies_id = c.id
    AND rf.rn = 1;

内容的提问来源于stack exchange，提问作者Carol.Kar