如何用SQL筛选符合状态且无本年支付记录的Contact Registration记录

现有两个数据表：Contact Registration表（联系人注册表）与Payments表（支付表），一条联系人注册记录可对应多条支付记录。

• Contact Registration表字段：ContactRegId（注册ID）、RegStatus（注册状态）、RenewalStatus（续费状态）、ExpiryDate（到期日期）
• Payments表字段：ContactRegId（关联注册ID）、CreatedOn（创建时间）、PaymentId（支付ID）

需求：从Contact Registration表中筛选满足以下条件的记录：

1. RegStatus为'Suspended'（暂停）或'Registered'（已注册）
2. RenewalStatus为'Pending'（待处理）
3. 对应Payments表中无本年创建的支付记录

预期输出仅包含ContactRegId为123456的联系人注册记录。

用户尝试的SQL语句（未生效）：

select distinct cr.ContactRegId , COUNT(PaymentId) as count1
from contactregistration cr left join payments p
on cr.ContactRegId = p.ContactRegId 
where (cr.RegStatus   = 'Registerd' or cr.RegStatus   = 'Suspended')
and cr.RenewalStatus = 'Pending'
and  YEAR(p.CreatedOn) = YEAR(getdate()) 
group by cr.ContactRegId having COUNT(PaymentId) = 0

你的SQL核心问题是：WHERE子句里的YEAR(p.CreatedOn) = YEAR(getdate())会过滤掉左连接中无对应支付记录的行（此时p.CreatedOn为NULL，NULL与任何值比较都不成立），导致永远查不到符合条件的记录。另外注意原语句中Registerd是拼写错误，正确应为Registered，需同步修正。

以下是两种可行的正确写法：

写法一：左连接+ON子句过滤支付时间 #

把本年支付记录的判断放到JOIN的ON子句中，这样左连接后，无本年支付记录的行p.ContactRegId会为NULL，再筛选该条件即可：

SELECT cr.ContactRegId
FROM contactregistration cr
LEFT JOIN payments p 
    ON cr.ContactRegId = p.ContactRegId 
    AND YEAR(p.CreatedOn) = YEAR(GETDATE())
WHERE (cr.RegStatus = 'Registered' OR cr.RegStatus = 'Suspended')
  AND cr.RenewalStatus = 'Pending'
  AND p.ContactRegId IS NULL;

写法二：NOT EXISTS子查询 #

逻辑更直观，直接判断当前注册ID不存在本年的支付记录：

SELECT cr.ContactRegId
FROM contactregistration cr
WHERE (cr.RegStatus = 'Registered' OR cr.RegStatus = 'Suspended')
  AND cr.RenewalStatus = 'Pending'
  AND NOT EXISTS (
    SELECT 1 
    FROM payments p 
    WHERE p.ContactRegId = cr.ContactRegId 
      AND YEAR(p.CreatedOn) = YEAR(GETDATE())
  );

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