SQL需求：排除含活跃合同客户，筛选符合条件的客户数据

我们需要从系统中筛选两类客户：

• 无合同且账户有余额的客户
• 所有合同均为非活跃状态（状态值'A'代表活跃）且账户有余额的客户

核心要求：必须完全排除存在至少一份活跃合同的客户。当前使用的查询语句无法满足该需求。

现有查询语句 #

SELECT T0.balance, *
FROM cust t0
left outer join Contracts t1 on t1.U_Code = t0.Code
Where (t1.U_Status != 'A'  or t1.U_Status is NULL) and t0.balance != 0 and CardType = 'c'
order by t0.balance

示例数据 #

期望查询结果 #

（注：A2因存在活跃合同被排除）

问题分析 #

原查询的缺陷在于：使用LEFT JOIN后，只要客户有任意一份非活跃合同（或无合同），就会被纳入结果。但像A2这类同时存在活跃和非活跃合同的客户，其非活跃合同的记录会被错误筛选出来，违反了"完全排除有活跃合同客户"的核心要求。

优化后的查询语句 #

方案一：使用NOT EXISTS排除活跃客户 #

逻辑清晰，性能表现优异（适合合同表有索引的场景）：

SELECT t0.balance, t0.*, t1.*
FROM cust t0
LEFT JOIN Contracts t1 ON t1.U_Code = t0.Code
WHERE t0.balance != 0 
  AND t0.CardType = 'c'
  AND NOT EXISTS (
    SELECT 1 
    FROM Contracts t2 
    WHERE t2.U_Code = t0.Code 
      AND t2.U_Status = 'A'
  )
ORDER BY t0.balance;

方案二：分组聚合验证合同状态 #

适合需要同时聚合合同其他信息的场景：

SELECT t0.balance, t0.*, t1.*
FROM cust t0
LEFT JOIN Contracts t1 ON t1.U_Code = t0.Code
WHERE t0.balance != 0 
  AND t0.CardType = 'c'
GROUP BY t0.Code, t0.balance, t0.CardType, t1.U_Code, t1.U_Status
HAVING MAX(CASE WHEN t1.U_Status = 'A' THEN 1 ELSE 0 END) = 0
ORDER BY t0.balance;

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