如何筛选当前仅使用payerorder为9的ginny付款方的客户

之前编写的SQL仅能筛选出从未有其他付款方、仅使用payerorder=9的ginny付款方的客户（如CustomerID 00003），但无法覆盖那些曾有过期付款方、当前仅保留ginny的客户（如CustomerID 00004）。系统会隐藏当前日期之前已过期的付款方信息，需要调整SQL逻辑，过滤过期数据后得到符合要求的客户列表。

原有SQL #

SELECT DISTINCT
       YT.CustomerID,
       YT.payerorder,
       YT.payername
FROM dbo.YourTable YT
WHERE YT.payerorder = 9
  AND NOT EXISTS (SELECT 1
                  FROM dbo.YourTable E
                  WHERE E.payername= YT.payername
                    AND E.payerorder <> 9);

数据结构示例 #

期望结果 #

同时返回CustomerID 00003和00004，现有SQL仅返回00003。

实际表关联SQL #

SELECT 
   "CUSTOMERPAYER"."payerorder",
 "CUSTOMER"."client_id",
   "PAYER"."payername",
 "CUSTOMERPAYER"."PayerEndDate"
FROM "dbo"."CUSTOMER" AS "CUSTOMER"

LEFT OUTER JOIN "dbo"."Payer" AS "PAYER"
ON (
   "CUSTOMER"."payer_id" = "PAYER"."payer_id"
)
LEFT OUTER JOIN "dbo"."CUSTOMERPAYER" AS "CUSTOMERPAYER"
ON (
   "CUSTOMER"."customerpayer_id" = "CUSTOMERPAYER"."customerpayer_id"
)

解决方案 #

核心逻辑是先过滤掉已过期的付款方记录，再判断客户的有效付款方是否仅为ginny（payerorder=9）。基于你的表关联结构，调整后的SQL如下：

WITH ActivePayers AS (
    -- 筛选所有未过期的付款方记录
    SELECT 
        C.client_id AS CustomerID,
        CP.payerorder,
        P.payername,
        CP.PayerEndDate
    FROM dbo.CUSTOMER C
    LEFT JOIN dbo.PAYER P ON C.payer_id = P.payer_id
    LEFT JOIN dbo.CUSTOMERPAYER CP ON C.customerpayer_id = CP.customerpayer_id
    -- 过滤条件：无结束日期（永久有效）或结束日期未到当前日期
    WHERE CP.PayerEndDate IS NULL OR CP.PayerEndDate >= CAST(GETDATE() AS DATE)
)
SELECT DISTINCT
    AP.CustomerID,
    AP.payerorder,
    AP.payername
FROM ActivePayers AP
WHERE AP.payerorder = 9 AND AP.payername = 'ginny'
-- 确保该客户的所有有效付款方仅为ginny
AND NOT EXISTS (
    SELECT 1
    FROM ActivePayers AP2
    WHERE AP2.CustomerID = AP.CustomerID
    AND (AP2.payerorder <> 9 OR AP2.payername <> 'ginny')
);

逻辑说明 #

1. CTE ActivePayers：先排除过期的付款方数据，只保留当前有效的付款方记录。
2. 主查询：先定位到使用ginny的客户，再通过NOT EXISTS验证该客户没有其他未过期的付款方，确保当前仅使用ginny。

这样即可同时包含00003（从未有其他付款方）和00004（曾有过期付款方，当前仅ginny有效）的客户。

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