SQL Server 查询相同 payment id 但不同 payment type 的记录

2026-10-24支付老李

结论

先按 payment_Id 分组，用 COUNT(DISTINCT PaymentType) > 1 找出包含多种支付类型的 payment_Id，再将这些 ID 关联回原始结果集。

分组的作用只是判断某个 payment_Id 下是否有不同的支付类型，最终查询仍需返回原始数据中的全部明细行。不要直接过滤某一种 PaymentType，否则需要保留的 CHECK 行也会被删掉。

推荐写法

使用 CTE 生成一次基础数据，再筛选符合条件的 payment_Id：

WITH PaymentData AS
(
    SELECT
        CAST(RTP.ExternalId AS varchar(25)) AS transID,
        CASE RTP.Activity
            WHEN 0 THEN 'Payment'
            WHEN 1 THEN 'Void'
        END AS transType,
        RTP.TransactionCreateDate AS createdDate,
        RTP.Amount AS paymentAmount_rtp,
        T.Amount AS paymentAmount_tenders,
        CASE
            WHEN RTP.Amount = T.Amount THEN 1
            ELSE 0
        END AS DesiredResult,
        RTP.EffectiveDate AS paymentDate,
        RTP.Id AS payment_Id,
        RTP.TransactionBatchId AS batchId,
        T.TenderTypeId AS TenderType,
        TType.Name AS PaymentType
    FROM Reporting.TransactionPayments AS RTP
    INNER JOIN dbo.Tenders AS T
        ON T.TransactionId = RTP.TransactionId
    INNER JOIN dbo.TenderTypes AS TType
        ON TType.Id = T.TenderTypeId
    WHERE RTP.TransactionCreateDate >= '20191201'
),
QualifiedPayments AS
(
    SELECT payment_Id
    FROM PaymentData
    GROUP BY payment_Id
    HAVING COUNT(DISTINCT PaymentType) > 1
)
SELECT
    P.transID,
    P.transType,
    P.createdDate,
    P.paymentAmount_rtp,
    P.paymentAmount_tenders,
    P.DesiredResult,
    P.paymentDate,
    P.payment_Id,
    P.batchId,
    P.TenderType,
    P.PaymentType
FROM PaymentData AS P
INNER JOIN QualifiedPayments AS Q
    ON Q.payment_Id = P.payment_Id
ORDER BY
    P.payment_Id,
    P.TenderType;

对于示例数据：

payment_Id = 1072405 只有 CASH。即使有两行，也会被排除。
payment_Id = 1072667 同时有 CASH 和 CHECK，所以对应的三行都会返回。

更稳妥的判断字段

如果不同的 TenderTypeId 代表不同的支付类型，建议直接统计 TenderType，以免名称中的空格、大小写或数据库排序规则影响判断：

HAVING COUNT(DISTINCT TenderType) > 1

完整的分组部分可以改成：

QualifiedPayments AS
(
    SELECT payment_Id
    FROM PaymentData
    GROUP BY payment_Id
    HAVING COUNT(DISTINCT TenderType) > 1
)

通常，ID 比显示名称更适合用来判断类型是否相同。

使用 `EXISTS` 的写法

也可以用 EXISTS 判断同一个 payment_Id 下是否存在支付类型不同的另一条记录：

WITH PaymentData AS
(
    SELECT
        CAST(RTP.ExternalId AS varchar(25)) AS transID,
        CASE RTP.Activity
            WHEN 0 THEN 'Payment'
            WHEN 1 THEN 'Void'
        END AS transType,
        RTP.TransactionCreateDate AS createdDate,
        RTP.Amount AS paymentAmount_rtp,
        T.Amount AS paymentAmount_tenders,
        CASE
            WHEN RTP.Amount = T.Amount THEN 1
            ELSE 0
        END AS DesiredResult,
        RTP.EffectiveDate AS paymentDate,
        RTP.Id AS payment_Id,
        RTP.TransactionBatchId AS batchId,
        T.TenderTypeId AS TenderType,
        TType.Name AS PaymentType
    FROM Reporting.TransactionPayments AS RTP
    INNER JOIN dbo.Tenders AS T
        ON T.TransactionId = RTP.TransactionId
    INNER JOIN dbo.TenderTypes AS TType
        ON TType.Id = T.TenderTypeId
    WHERE RTP.TransactionCreateDate >= '20191201'
)
SELECT P.*
FROM PaymentData AS P
WHERE EXISTS
(
    SELECT 1
    FROM PaymentData AS P2
    WHERE P2.payment_Id = P.payment_Id
      AND P2.TenderType <> P.TenderType
)
ORDER BY
    P.payment_Id,
    P.TenderType;

对于当前行，只要同一 payment_Id 下存在另一条 TenderType 不同的记录，查询就会保留当前行。因此，CASH 和 CHECK 都会返回。

原查询的问题

原来的条件是：

HAVING COUNT(*) > 1

它只能说明同一个 payment_Id 有多条记录，不能判断这些记录的 PaymentType 是否不同。因此，两条都是 CASH 的记录也会满足条件。

应改为：

HAVING COUNT(DISTINCT PaymentType) > 1

更推荐使用：

HAVING COUNT(DISTINCT TenderType) > 1

注意事项

不要在最终查询中添加 PaymentType <> 'CHECK' 或类似条件。这类条件会删除某一种支付类型，无法判断一笔付款是否包含多种类型。
如果需要返回全部原始行，不要随意使用 SELECT DISTINCT，否则字段完全相同的两条真实记录可能被合并。只有关联产生了非预期的重复数据时，才需要分析重复的来源。
日期建议采用无歧义格式，例如 '20191201'。
不要在日期列上使用 CAST(RTP.TransactionCreateDate AS DATE)。直接比较原始列通常更有利于使用索引：

WHERE RTP.TransactionCreateDate >= '20191201'

如果 PaymentType 可能为 NULL，需要注意 COUNT(DISTINCT PaymentType) 不会统计 NULL。如果业务上要把 NULL 视为一种独立类型，应先明确相应规则，再使用 COALESCE 处理。