如何编写SQL查询多对多关联ID全部在权限数组内的条目

现有两张表需要关联查询，筛选出当前用户有权限查看所有关联组件的条目：

• 条目表字段：entry_id、entry_name、other_info
• 条目-组件多对多关联表字段：entry_id、component_id
• 示例用户权限数组为[1,2,3]，要求返回所有关联的component_id均在权限数组内的条目entry_id、entry_name字段，示例场景下仅返回entry_id为2、3的条目，关联了权限外组件4的entry_id=1会被过滤。

核心逻辑：对每个条目，统计它关联的总组件数，再统计它关联的组件中属于用户权限范围内的数量，两个数量相等即代表该条目所有组件都在权限内。

通用写法（兼容MySQL、Oracle等多数主流数据库） #

SELECT e.entry_id, e.entry_name
FROM 条目表 e
JOIN 条目组件关联表 ec ON e.entry_id = ec.entry_id
GROUP BY e.entry_id, e.entry_name
-- 总关联组件数 = 权限内组件数时才保留
HAVING COUNT(ec.component_id) = SUM(CASE WHEN ec.component_id IN (1,2,3) THEN 1 ELSE 0 END)

PostgreSQL数组语法简化写法 #

如果使用PostgreSQL数据库，可直接用数组包含运算符简化逻辑：

SELECT e.entry_id, e.entry_name
FROM 条目表 e
JOIN 条目组件关联表 ec ON e.entry_id = ec.entry_id
GROUP BY e.entry_id, e.entry_name
-- <@ 运算符表示左侧数组所有元素都包含在右侧数组中
HAVING ARRAY_AGG(ec.component_id) <@ ARRAY[1,2,3]::int[]

该逻辑也兼容条目未关联任何组件的场景：总关联数为0，权限内匹配数也为0，会被正常返回，符合权限判断逻辑。

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