如何在Oracle SQL中查询满足所有子表行匹配条件的主表数据？

需求说明 #

需要从Table1中筛选出满足以下条件的记录：

• 关联的所有Table2子表行的details值仅包含R1和R2
• 子表行同时包含R1和R2这两个值（避免仅含其中一种的情况）

对应示例数据，最终仅需返回Table1中pkcol=10、seq=1的记录。

方法一：分组聚合+Having条件 #

通过分组统计子表行的特征，过滤出符合要求的父表记录：

SELECT t1.pkcol, t1.seq
FROM Table1 t1
JOIN Table2 t2 ON t1.pkcol = t2.fkcol_from_Table1
GROUP BY t1.pkcol, t1.seq
-- 排除存在非R1/R2子表行的父表记录
HAVING COUNT(CASE WHEN t2.details NOT IN ('R1', 'R2') THEN 1 END) = 0
-- 确保子表同时包含R1和R2两种值
AND COUNT(DISTINCT t2.details) = 2;

逻辑说明：

1. 关联Table1和Table2，获取所有父表-子表的关联行
2. 按Table1的主键和seq分组，统计每组子表行的情况
3. 第一个Having条件：统计子表中不属于{R1,R2}的行数，若为0则说明所有子表行都符合要求
4. 第二个Having条件：统计子表中不同details值的数量，等于2则说明同时包含R1和R2

方法二：EXISTS/ NOT EXISTS子查询 #

通过多个子查询分别验证条件，逻辑更直观：

SELECT t1.pkcol, t1.seq
FROM Table1 t1
-- 验证存在关联的R1子表行
WHERE EXISTS (SELECT 1 FROM Table2 t2 WHERE t2.fkcol_from_Table1 = t1.pkcol AND t2.details = 'R1')
-- 验证存在关联的R2子表行
AND EXISTS (SELECT 1 FROM Table2 t2 WHERE t2.fkcol_from_Table1 = t1.pkcol AND t2.details = 'R2')
-- 验证不存在关联的非R1/R2子表行
AND NOT EXISTS (SELECT 1 FROM Table2 t2 WHERE t2.fkcol_from_Table1 = t1.pkcol AND t2.details NOT IN ('R1', 'R2'));

逻辑说明：

1. 前两个EXISTS确保父表对应的子表中同时存在R1和R2的记录
2. NOT EXISTS确保父表对应的子表中没有任何不属于{R1,R2}的记录
3. 三个条件同时满足时，返回对应的Table1记录

示例数据验证 #

针对题目中的示例数据：

• Table1.pkcol=10：子表有R1、R2，无其他值，符合所有条件，会被查询返回
• Table1.pkcol=20：子表仅含R1，缺少R2，被排除
• Table1.pkcol=30：子表含R4（不在目标集合），被排除

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