MySQL中关联含多外键记录的表并获取最新状态的查询方法

这里提供两种可行的SQL查询方案，适配不同数据库环境：

方案一：使用子查询（兼容多数数据库） #

先通过子查询找到目标分销商的最新状态变更时间，再关联两张表筛选对应记录：

SELECT 
    d.ID AS id,
    d.`distributor name` AS distributor_name,
    ds.status,
    ds.status_changed_on
FROM distributor d
JOIN distributor_status ds 
    ON d.ID = ds.distributor_id
WHERE d.ID = 1
AND ds.status_changed_on = (
    SELECT MAX(status_changed_on) 
    FROM distributor_status 
    WHERE distributor_id = d.ID
);

方案二：使用窗口函数（适用于MySQL 8+、PostgreSQL、SQL Server等） #

利用ROW_NUMBER()窗口函数给每个分销商的状态记录按变更时间倒序编号，最新记录编号为1，直接筛选即可：

SELECT 
    id,
    distributor_name,
    status,
    status_changed_on
FROM (
    SELECT 
        d.ID AS id,
        d.`distributor name` AS distributor_name,
        ds.status,
        ds.status_changed_on,
        ROW_NUMBER() OVER (PARTITION BY d.ID ORDER BY ds.status_changed_on DESC) AS rn
    FROM distributor d
    JOIN distributor_status ds 
        ON d.ID = ds.distributor_id
    WHERE d.ID = 1
) t
WHERE rn = 1;

补充说明 #

• 方案一兼容性更强，适合不支持窗口函数的旧版数据库；
• 方案二更灵活，若要批量查询所有分销商的最新状态，只需移除内层查询的WHERE d.ID = 1条件即可。

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