MySQL查询：按用户ID获取当前/最后所属组ID

需求场景 #

现有MySQL数据库包含两张核心表：

• person：存储用户个人信息
• member_group：存储用户-组关联关系，字段包括user_id、group_id、in_group_begin（入组日期）、in_group_end（出组日期）、自增主键tbl_id。业务规则如下：
  • 用户当前在职时，in_group_end为NULL
  • 用户离开组（含转组、离职）时，in_group_end填充离开日期
  • 转组会生成新的member_group记录；已离职用户的所有in_group_end均有值

需查询所有用户，返回结果要求：

• 在职用户：显示当前所在组ID
• 离职用户：显示最后所属的组ID

解决方案 #

方法一：窗口函数实现（MySQL 8.0+） #

利用ROW_NUMBER()窗口函数按用户分组排序，优先保留在职记录，离职用户则按出组日期倒序取最新记录：

SELECT 
    p.*,
    mg.group_id AS latest_group_id
FROM 
    person p
LEFT JOIN (
    SELECT 
        user_id,
        group_id,
        ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY CASE WHEN in_group_end IS NULL THEN 0 ELSE 1 END, in_group_end DESC
        ) AS rn
    FROM 
        member_group
) mg ON p.user_id = mg.user_id AND mg.rn = 1;

逻辑解析：

• 子查询内按user_id分组，通过ORDER BY将在职记录（in_group_end IS NULL）排在最前，离职用户则取最晚的in_group_end记录
• 关联person表时仅取每个用户的第一条排序记录（rn=1），即为目标组ID

方法二：自增主键关联（兼容全版本MySQL） #

借助自增tbl_id的特性，每个用户的最新组记录对应最大的tbl_id，通过关联直接获取目标组ID：

SELECT 
    p.*,
    mg.group_id AS latest_group_id
FROM 
    person p
LEFT JOIN (
    SELECT 
        user_id,
        MAX(tbl_id) AS max_tbl_id
    FROM 
        member_group
    GROUP BY 
        user_id
) mg_max ON p.user_id = mg_max.user_id
LEFT JOIN member_group mg ON mg_max.max_tbl_id = mg.tbl_id;

逻辑解析：

• 先按用户分组，获取每个用户的最大tbl_id（最新组记录的标识）
• 通过该tbl_id关联member_group表，拿到对应的组ID
• 用LEFT JOIN确保所有person表用户都能被返回（包括无组记录的用户）

优化版：优先保障在职记录（兼容全版本） #

若需避免异常数据干扰（如用户存在在职记录后又生成离职记录），可优先取在职记录，无在职记录时再取最新离职记录：

SELECT 
    p.*,
    mg.group_id AS latest_group_id
FROM 
    person p
LEFT JOIN (
    SELECT 
        user_id,
        COALESCE(
            MAX(CASE WHEN in_group_end IS NULL THEN tbl_id ELSE NULL END),
            MAX(tbl_id)
        ) AS target_tbl_id
    FROM 
        member_group
    GROUP BY 
        user_id
) mg_target ON p.user_id = mg_target.user_id
LEFT JOIN member_group mg ON mg_target.target_tbl_id = mg.tbl_id;

逻辑解析：

• 用COALESCE先尝试获取用户的在职记录tbl_id，若不存在则取最大的tbl_id（最新离职记录）

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