fetch single row with value true for grouped records MySQL

mzsu5hc0 于 12个月前发布在 Mysql

关注(0)|答案(1)|浏览(95)

我的样本数据：

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和结果应该有值true的重复permission_id行。正如我们所看到的，查询是按permission_id分组的，值为true的行被选择为permission_id 10，而对于其他行，permission_id没有被重复，所以值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
------------------------------------------------------------------

型
下面是我试图获取上述结果的查询。

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

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

mysql

来源：https://stackoverflow.com/questions/77727616/fetch-single-row-with-value-true-for-grouped-records-mysql

1条答案

按热度按时间

tktrz96b1#

您可以简单地根据permission_id和access_granted按降序对行进行排序，然后根据permission_id选择不同的行。类似于：

WITH ordered_rows AS (
  SELECT id, role_id, permission_id, access_granted, user_id,
         ROW_NUMBER() OVER (PARTITION BY permission_id ORDER BY access_granted DESC) as row_num
  FROM temp_permission
)
SELECT id, role_id, permission_id, access_granted, user_id
FROM ordered_rows
WHERE row_num = 1;

字符串
DB Fiddle

赞(0）回复(0）举报 12个月前

我来回答

fetch single row with value true for grouped records MySQL

1条答案

相关问题

热门标签

最新问答