MySQL分组查询：按permission_id取access_granted为true的唯一行

表结构 #

CREATE TABLE temp_permission (
    id int auto_increment primary key,
    role_id int,
    permission_id int,
    access_granted tinyInt(1),
    user_id int
);

测试数据 #

插入语句 #

INSERT INTO `temp_permission` (`id`, `role_id`, `permission_id`, `access_granted`, `user_id`) VALUES ('1', '1', '10', '1', '115');
INSERT INTO `temp_permission` (`id`, `role_id`, `permission_id`, `access_granted`, `user_id`) VALUES ('2', '1', '5', '1', '115');
INSERT INTO `temp_permission` (`id`, `role_id`, `permission_id`, `access_granted`, `user_id`) VALUES ('3', '2', '10', '0', '115');
INSERT INTO `temp_permission` (`id`, `role_id`, `permission_id`, `access_granted`, `user_id`) VALUES ('4', '2', '8', '0', '115');

样本数据 #

需求 #

按permission_id分组处理：

• 同一permission_id有多条记录时，仅保留access_granted=1的行
• permission_id唯一时，直接保留原行

预期结果 #

原尝试语句的问题 #

原语句在MySQL默认的ONLY_FULL_GROUP_BY模式下会报错，因为id、role_id、user_id等非聚合列未包含在GROUP BY子句中，无法保证返回值的确定性：

select id, role_id, permission_id,
case when max(access_granted) = true then true else false end as access_granted,
user_id as user_id  
from temp_permission group by permission_id;

优化后的稳定查询语句 #

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

利用ROW_NUMBER()窗口函数，按权限分组后优先排序授权状态为1的行，再取每组第一行：

SELECT id, role_id, permission_id, access_granted, user_id
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY permission_id 
            ORDER BY access_granted DESC, id ASC
        ) AS rn
    FROM temp_permission
) t
WHERE rn = 1;

• 说明：ORDER BY access_granted DESC确保授权行优先；id ASC保证同一权限有多条授权行时，取id最小的记录（可根据业务调整排序规则）。

方法2：子查询关联实现（兼容MySQL 5.x） #

先通过子查询获取每个权限的最高授权状态，再关联原表筛选对应行：

SELECT tp.id, tp.role_id, tp.permission_id, tp.access_granted, tp.user_id
FROM temp_permission tp
INNER JOIN (
    SELECT 
        permission_id,
        MAX(access_granted) AS max_access
    FROM temp_permission
    GROUP BY permission_id
) t ON tp.permission_id = t.permission_id
WHERE tp.access_granted = t.max_access;

• 说明：若同一权限有多条授权行，此语句会返回所有符合条件的行；如需仅返回一行，可添加条件AND tp.id = (SELECT MIN(id) FROM temp_permission WHERE permission_id = t.permission_id AND access_granted = t.max_access)。

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