多表关联SQL查询：按条件筛选重复Account_Number的指定记录

核心需求明确：关联三张表后，筛选Relation_Code为'C'或'D'的记录；若同一Account_Number下同时存在这两类记录，仅保留'D'的条目。

方法一：窗口函数实现（推荐，适配MySQL 8+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

通过ROW_NUMBER()按Account_Number分组，给'D'类型记录赋予最高优先级（行号为1），'C'类型赋予行号2，最终只取行号为1的记录：

SELECT t.Party_Number, t.Account_Number, t.Relation_Code, t.Email_Code,
       t.Date, t.Email
FROM (
    SELECT A.Party_Number, A.Account_Number, A.Relation_Code, A.Email_Code,
           B.Date, C.Email,
           ROW_NUMBER() OVER (PARTITION BY A.Account_Number 
                              ORDER BY CASE WHEN A.Relation_Code = 'D' THEN 1 ELSE 2 END) AS rn
    FROM TableA A
    JOIN TableB B ON A.Account_Number = B.Account_Number
    JOIN TableC C ON A.Party_Number = C.Party_Number
    WHERE A.Relation_Code IN ('C', 'D')
) t
WHERE t.rn = 1
ORDER BY t.Account_Number;

方法二：子查询判断（兼容旧版本数据库） #

先找出所有存在'D'类型记录的Account_Number，再分情况筛选：要么是'D'类型记录，要么是'C'类型且对应账号无'D'记录：

SELECT A.Party_Number, A.Account_Number, A.Relation_Code, A.Email_Code,
       B.Date, C.Email
FROM TableA A
JOIN TableB B ON A.Account_Number = B.Account_Number
JOIN TableC C ON A.Party_Number = C.Party_Number
WHERE A.Relation_Code IN ('C', 'D')
AND (
    A.Relation_Code = 'D'
    OR NOT EXISTS (
        SELECT 1 FROM TableA A2
        WHERE A2.Account_Number = A.Account_Number
        AND A2.Relation_Code = 'D'
    )
)
ORDER BY A.Account_Number;

测试结果验证 #

针对你提供的样本数据，两种方法都会返回以下结果：

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