如何基于关联行条件筛选表中指定状态的最新邮件地址？

这个需求在用户关联数据管理场景里特别常见，我给你整理几种实用的实现方式，覆盖不同数据库环境：

假设你的表名为user_emails，核心字段包括：parent_id（关联ID）、email（邮箱地址）、status（状态）、create_date（创建时间）。

方案1：窗口函数实现（推荐，支持MySQL 8+/PostgreSQL/SQL Server等） #

窗口函数是最清晰高效的写法，能直接按parent_id分组排序，精准定位每个组内最新的Active记录：

WITH ranked_active_emails AS (
    SELECT 
        parent_id,
        email,
        -- 按parent_id分组，每组内按创建时间倒序排号，最新的记录排第1
        ROW_NUMBER() OVER (
            PARTITION BY parent_id 
            ORDER BY create_date DESC
        ) AS record_rank
    FROM user_emails
    WHERE status = 'active' -- 先过滤出Active状态的记录
)
SELECT parent_id, email
FROM ranked_active_emails
WHERE record_rank = 1
-- 若只需要特定parent_id，添加条件：AND parent_id = 111

针对你的示例测试 #

代入你提供的两条数据：

• parent_id=111的m@y.com因状态为unsubscribed会被过滤
• 剩下的m@x.com是该parent_id下唯一的Active记录，自然会被选中

方案2：聚合子查询（兼容旧版MySQL 5.x等无窗口函数的数据库） #

如果你的数据库不支持窗口函数，可以用子查询先找出每个parent_id的最新Active记录时间，再关联原表获取邮箱：

SELECT ue.parent_id, ue.email
FROM user_emails ue
INNER JOIN (
    -- 子查询：找出每个parent_id对应的最新Active记录创建时间
    SELECT 
        parent_id,
        MAX(create_date) AS latest_active_date
    FROM user_emails
    WHERE status = 'active'
    GROUP BY parent_id
) latest_records 
    ON ue.parent_id = latest_records.parent_id 
    AND ue.create_date = latest_records.latest_active_date
    AND ue.status = 'active' -- 再次过滤确保是Active记录
-- 如需指定parent_id，添加：AND ue.parent_id = 111

额外说明 #

如果同一个parent_id下有多条创建时间完全相同的Active记录，两种方案都会返回所有符合时间的记录。若想强制只返回一条，可以在窗口函数的ORDER BY里再加一个唯一字段（比如email或主键），确保排序唯一。

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