mysql - MySQL 中从关系表中获取所有关联值同时过滤相同值的最有效方法

Question

简化 我在 MySQL 中有两个表:一个包含一种人实体，一个关系表将多个权限与两个人实体相关联。表格看起来像:

人

person_id | person_name
1         | Michael
2         | Kevin

人2权

person_id | right_id
1         | 1
1         | 2
1         | 4
2         | 1
2         | 2

我现在想要实现的是让所有人包括所有相关权利，这些权利至少具有定义的权利 - 在本例中为 right_id 1 和 4。

到目前为止，我所拥有的是带有子选择的查询，但我想知道是否有更有效的方法可以在没有子选择的情况下实现我的目标，因为 MySQL 在加入子选择时无法使用索引。这是我的查询:

SELECT person_name, GROUP_CONCAT(`person2right`.`right_id`) as `all_rights` 
FROM `person` 
LEFT JOIN `person2right` ON `person`.`person_id` = `person2right`.`person_id` 
LEFT JOIN (
    SELECT `person_id` FROM `person2right` WHERE `right_id` IN (1, 4) 
    GROUP BY `person_id` HAVING COUNT(`person2right`.`right_id`) >= 2
) as `p2r` ON `person`.`person_id` = `p2r`.`person_id` 
WHERE `p2r`.`person_id` IS NOT NULL GROUP BY `person_id`

也许有人有一个不使用子查询的想法。感谢您的帮助。

提前致谢!

Answer 1

这将只选择同时拥有权利 1 和 4 的人(及其所有相关权利)。请注意，它与您的查询不同，因为您的查询选择所有人(无论他们的权利如何)并且仅选择他们的相关权利，如果他们拥有权利 1 和 4。

SELECT person_name, GROUP_CONCAT(`person2right`.`right_id`) as `all_rights` 
FROM `person` 
JOIN `person2right` ON `person`.`person_id` = `person2right`.`person_id` 
GROUP BY `person`.`person_id`
HAVING SUM(`right_id` = 4) > 0 AND SUM(`right_id` = 1) > 0

编辑:如果 person2right 中的行是唯一的，那么您可以将 having 子句更改为

HAVING SUM(`right_id` IN (1,4)) = 2