MySQL中如何按modified字段筛选用户最新的会员状态记录？

问题分析 #

你之前的查询失效的核心原因是子查询中的表名拼写错误：将wp_memberships_users误写为wp__memberships_users（多了一个下划线），导致子查询无法正确计算每个用户的最新modified值，过滤条件失效，因此仍返回多条重复记录。

修正后的查询方案 #

方案1：修正表名并保留IN语法

SELECT 
    e.user_id,
    e.status, 
    e.modified,
    m1.meta_value as fname,
    m2.meta_value as lname,
    m3.meta_value as company
FROM
    wp_memberships_users e
LEFT JOIN wp_usermeta  m ON e.user_ID = m.user_id AND m.meta_key = '1'
LEFT JOIN wp_usermeta m1 ON e.user_ID = m1.user_id AND m1.meta_key = 'first_name'
LEFT JOIN wp_usermeta m2 ON e.user_ID = m2.user_id AND m2.meta_key = 'last_name' 
LEFT JOIN wp_usermeta m3 ON e.user_ID = m3.user_id AND m3.meta_key = 'company_name' 
WHERE
    (e.user_id, e.modified) IN (
        SELECT user_id, MAX(modified)
        FROM wp_memberships_users
        WHERE status IN ('active', 'expired') -- 可选：仅在指定状态中取最新记录
        GROUP BY user_id
    )
    AND e.status IN ('active', 'expired') -- 若子查询未加状态过滤，需保留此条件

方案2：用JOIN替代IN（推荐，大表性能更优）

SELECT 
    e.user_id,
    e.status, 
    e.modified,
    m1.meta_value as fname,
    m2.meta_value as lname,
    m3.meta_value as company
FROM
    wp_memberships_users e
-- 关联子查询获取每个用户的最新modified记录
JOIN (
    SELECT user_id, MAX(modified) AS latest_modified
    FROM wp_memberships_users
    WHERE status IN ('active', 'expired') -- 可选：仅在指定状态中取最新记录
    GROUP BY user_id
) AS latest ON e.user_id = latest.user_id AND e.modified = latest.latest_modified
LEFT JOIN wp_usermeta  m ON e.user_ID = m.user_id AND m.meta_key = '1'
LEFT JOIN wp_usermeta m1 ON e.user_ID = m1.user_id AND m1.meta_key = 'first_name'
LEFT JOIN wp_usermeta m2 ON e.user_ID = m2.user_id AND m2.meta_key = 'last_name' 
LEFT JOIN wp_usermeta m3 ON e.user_ID = m3.user_id AND m3.meta_key = 'company_name' 
-- 若子查询已加状态过滤，此处可省略WHERE条件
WHERE e.status IN ('active', 'expired')

状态过滤逻辑说明 #

• 若需要仅在active/expired状态的记录中取每个用户最新的一条：在子查询中加入WHERE status IN ('active', 'expired')，外层WHERE可省略。
• 若需要取每个用户所有状态中最新的一条，再过滤该记录是否为active/expired：子查询中不要加状态过滤，保留外层WHERE条件。

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