一对多关系数据库：如何按最新状态查询产品信息

场景1：返回所有曾处于目标状态的产品及对应状态记录 #

如果只要产品有过指定状态的历史，就返回对应的产品信息和该状态记录，直接通过JOIN关联两张表并筛选状态即可：

SELECT p.id AS product_id, p.name, sh.id AS history_id, sh.status, sh.note, sh.created_at
FROM products p
JOIN status_histories sh ON p.id = sh.product_id
WHERE sh.status = 'live'; -- 替换成你要搜索的状态，比如'reviewed'

搜索live的执行结果：

搜索reviewed的执行结果：

场景2：返回最新状态为目标状态的产品及对应状态记录 #

如果需要像示例那样，只返回当前最新状态是目标状态的产品（比如搜索reviewed只返回产品2，因为产品1的最新状态是live），有两种实现方式：

方法1：窗口函数（推荐，适用于MySQL 8+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

WITH latest_status AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY created_at DESC) AS rn
    FROM status_histories
)
SELECT 
    p.id AS product_id, 
    p.name, 
    ls.id AS history_id, 
    ls.status, 
    ls.note, 
    ls.created_at
FROM products p
JOIN latest_status ls ON p.id = ls.product_id
WHERE ls.rn = 1 AND ls.status = 'reviewed'; -- 替换成目标状态

搜索reviewed的执行结果：

搜索live的执行结果：

方法2：子查询（兼容旧版数据库） #

SELECT 
    p.id AS product_id, 
    p.name, 
    sh.id AS history_id, 
    sh.status, 
    sh.note, 
    sh.created_at
FROM products p
JOIN status_histories sh ON p.id = sh.product_id
WHERE sh.created_at = (
    SELECT MAX(created_at) 
    FROM status_histories 
    WHERE product_id = p.id
) AND sh.status = 'live'; -- 替换成目标状态

该方法执行结果与窗口函数方案一致。

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