为分组记录MySQL取取值为TRUE的单行

我的样本数据:

id     role_id    permission_id      access_granted      user_id
------------------------------------------------------------------
1         1            10                  1               115
------------------------------------------------------------------
2         1            5                   1               115   
------------------------------------------------------------------
3         2            10                  0               115
------------------------------------------------------------------
4         2            8                   0               115
------------------------------------------------------------------

GROUP BY WITH PERMISSION_ID，并且结果中重复的PERMISSION_ID应该具有值TRUE ROW.正如我们可以看到的那样，查询是按照permission_id分组的，并且为permission_id 10 Select 了值为True的行，对于其他行，权限id没有重复，因此 Select 的值为False.

预期结果:

id     role_id    permission_id      access_granted      user_id
------------------------------------------------------------------
1         1            10                  1               115
------------------------------------------------------------------
2         1            5                   1               115   
------------------------------------------------------------------
4         2            8                   0               115
------------------------------------------------------------------

下面是我try 获取上述结果的查询.

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;

需要在任何情况下都不会失败的优化和可信的查询.

Create table query个

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

Dummy data query for the table as per the screenshot个

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');

如果无法将数据插入ACCESS_GRANDED列，请替换1 = b'1' & 0 = b'0'