复合主键表按权限条件筛选ID的高效SQL实现方案咨询

问题背景 #

现有一张以id和permission为复合主键的表，记录主体的权限信息，表数据如下：

id    permission
1     'A'
1     'B'
2     'A'
3     'B'

需要筛选满足以下条件的id：

• 同时拥有权限'A'和'B'的id
• 拥有权限'A'但不拥有权限'B'的id

原实现使用exists子句可达成需求，但大表场景下性能较差（会多次遍历表），以下是更高效清晰的替代方案。

原实现代码 #

条件1（同时拥有A和B） #

SELECT DISTINCT t0.id FROM t t0
WHERE
EXISTS (SELECT 1 FROM t t1 WHERE t1.id = t0.id AND t1.permission = 'A')
AND EXISTS (SELECT 1 FROM t t1 WHERE t1.id = t0.id AND t1.permission = 'B');

条件2（拥有A但无B） #

SELECT DISTINCT t0.id FROM t t0
WHERE
EXISTS (SELECT 1 FROM t t1 WHERE t1.id = t0.id AND t1.permission = 'A')
AND NOT EXISTS (SELECT 1 FROM t t1 WHERE t1.id = t0.id AND t1.permission = 'B');

高效替代方案 #

方案1：分组聚合筛选（推荐大表使用） #

仅需遍历表一次，利用GROUP BY+HAVING完成筛选，性能最优。

满足条件1（同时拥有A和B）

SELECT id
FROM t
WHERE permission IN ('A', 'B')
GROUP BY id
HAVING COUNT(DISTINCT permission) = 2;

逻辑：先过滤出权限为A/B的记录，按id分组后，统计不同权限数量为2，说明该id同时拥有两种权限。

满足条件2（拥有A但无B）

SELECT id
FROM t
GROUP BY id
HAVING SUM(CASE WHEN permission = 'A' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN permission = 'B' THEN 1 ELSE 0 END) = 0;

逻辑：按id分组后，通过条件统计判断：存在A权限（统计值>0），且不存在B权限（统计值=0）。

方案2：自连接（适合依赖索引优化的场景） #

利用复合主键索引快速匹配关联记录，避免多次子查询遍历。

条件1（同时拥有A和B）

SELECT DISTINCT t_a.id
FROM t t_a
JOIN t t_b ON t_a.id = t_b.id
WHERE t_a.permission = 'A' AND t_b.permission = 'B';

条件2（拥有A但无B）

SELECT DISTINCT t_a.id
FROM t t_a
LEFT JOIN t t_b ON t_a.id = t_b.id AND t_b.permission = 'B'
WHERE t_a.permission = 'A' AND t_b.id IS NULL;

通用性能优化建议 #

确保表上存在复合主键索引(id, permission)，所有方案都能借助该索引大幅提升查询效率。

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