PDO实现关联独立状态表查询指定最新状态的所有订单

窗口函数实现（适配MySQL 8.0+、PostgreSQL、SQL Server等新版数据库） #

写法最简洁，通过窗口函数直接筛选每个订单的最新状态记录：

SELECT o.* 
FROM orders o
INNER JOIN (
    SELECT 
        order_id, 
        status,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY statusdate DESC, id DESC) AS rn
    FROM status
) s ON o.id = s.order_id
WHERE s.rn = 1 AND s.status = ?

排序规则额外加id DESC是为了规避同一时间点有多条状态变更记录时，取自增ID最大的那条作为最新记录，比仅按时间排序更准确。 #

低版本数据库兼容实现（支持MySQL 5.x等不支持窗口函数的场景） #

先通过分组子查询拿到每个订单最新的状态记录ID，再关联过滤：

SELECT o.*
FROM orders o
INNER JOIN status s ON o.id = s.order_id
INNER JOIN (
    SELECT MAX(id) AS latest_status_id 
    FROM status 
    GROUP BY order_id
) latest_s ON s.id = latest_s.latest_status_id
WHERE s.status = ?

无需JOIN的更轻量实现 #

可以用EXISTS子查询实现，不需要关联全量数据，查询性能更优：

SELECT o.*
FROM orders o
WHERE EXISTS (
    SELECT 1
    FROM status s
    WHERE s.order_id = o.id
    AND s.status = ?
    AND s.id = (SELECT MAX(id) FROM status WHERE order_id = o.id)
)

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