多对多关系中获取最新角色为role2的用户记录

开发者阿泽

2026-5-25

要获取所有最新角色为role2的用户，关键是先锁定每个用户的最新角色变更记录，再筛选出对应角色的用户。这里给你两种实用的SQL方案：

方法一：分组关联法

这种方式通过先找出每个用户的最新角色时间，再反向关联获取对应角色，逻辑清晰，适合大多数数据库：

SELECT u.user_id, u.user_name
FROM users u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN (
    -- 子查询：获取每个用户的最新角色变更时间
    SELECT user_id, MAX(created_at) AS latest_role_time
    FROM user_roles
    GROUP BY user_id
) latest_ur ON ur.user_id = latest_ur.user_id AND ur.created_at = latest_ur.latest_role_time
JOIN roles r ON ur.role_id = r.role_id
WHERE r.role_name = 'role2';

逻辑拆解：

子查询latest_ur先按用户分组，拿到每个用户最后一次变更角色的时间。
把user_roles和这个子查询关联，确保只取每个用户最新的那条角色记录。
关联roles表筛选出角色名为role2的记录，最后关联users表拿到用户信息（需要其他字段的话直接在SELECT里加就行）。

方法二：窗口函数法

如果你的数据库支持窗口函数（比如MySQL 8+、PostgreSQL、SQL Server等），这种方式更灵活，尤其适合需要处理复杂排序逻辑的场景：

SELECT user_id, user_name
FROM (
    SELECT 
        u.user_id, 
        u.user_name,
        r.role_name,
        -- 按用户分组，角色记录按创建时间倒序排名，最新的排第1
        ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY ur.created_at DESC) AS rn
    FROM users u
    JOIN user_roles ur ON u.user_id = ur.user_id
    JOIN roles r ON ur.role_id = r.role_id
) AS user_role_ranked
WHERE role_name = 'role2' AND rn = 1;