如何筛选Table_A中同一Rec_Id下部分匹配Table_B的非单条记录

现有数据表 #

Table_A #

Table_B #

筛选需求 #

需要从Table_A中筛选满足以下所有条件的记录：

• 同一Rec_Id对应的记录数大于1（排除单条记录的Rec_Id，比如Rec_Id=4）
• 该Rec_Id下至少有一条记录的Act_Cp与Table_B的sf_id匹配
• 该Rec_Id下至少有一条记录的Act_Cp与Table_B的sf_id不匹配
  最终只输出该Rec_Id下不匹配的那些记录。

原错误SQL #

原SQL仅适配初始数据，当新增全不匹配的Rec_Id（如Rec_Id=5）时会错误包含这类记录：

SELECT *
  FROM Table_A AS t1
 WHERE Rec_Id IN
       (SELECT Rec_Id FROM Table_B GROUP BY Rec_Id HAVING COUNT(*) > 1)
   AND NOT EXISTS
       (SELECT Sf_Id FROM Table_B AS t2 WHERE t1.Act_Cp = t2.Sf_Id)

修正后的SQL #

SELECT t1.*
FROM Table_A t1
WHERE 
    -- 筛选当前记录的Act_Cp不在Table_B中的数据
    NOT EXISTS (SELECT 1 FROM Table_B t2 WHERE t2.sf_id = t1.Act_Cp)
    -- 确保同一Rec_Id下存在至少一条匹配Table_B的记录
    AND EXISTS (
        SELECT 1 FROM Table_A t3 
        WHERE t3.Rec_Id = t1.Rec_Id 
        AND EXISTS (SELECT 1 FROM Table_B t4 WHERE t4.sf_id = t3.Act_Cp)
    )
    -- 确保同一Rec_Id下有多条记录，排除单条的情况
    AND EXISTS (
        SELECT 1 FROM Table_A t5 
        WHERE t5.Rec_Id = t1.Rec_Id 
        AND t5.Cust_Id <> t1.Cust_Id
    )

逻辑说明 #

• 第一个NOT EXISTS直接锁定我们要输出的、Act_Cp不匹配Table_B的记录；
• 第二个EXISTS用来排除那些全不匹配的Rec_Id（比如新增的Rec_Id=5），保证该Rec_Id下至少有一条能匹配Table_B；
• 第三个EXISTS确保当前Rec_Id下有至少两条记录，直接过滤掉单条记录的情况（比如Rec_Id=4）。

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