SQL Server 如何合并 Payment 与 Refunds 数据

2026-10-10支付老李

结论

这里适合使用 UNION ALL。第一部分查询已经发生的退款，第二部分查询 Refunds 表中等待执行的退款。

待执行退款的字段可以这样处理：

Amount 返回 0，表示款项尚未实际支付；
Difference 返回计划退款金额；
id 可以临时生成用于展示，但不能作为真实的业务主键。

查询示例

假设第二张表只保存尚未执行的退款：

WITH MaxPaymentId AS
(
    SELECT COALESCE(MAX(id), 0) AS MaxId
    FROM Payment
)
SELECT
    CAST(p.id AS bigint) AS id,
    p.Amount,
    p.BookingId,
    CAST(0 AS decimal(18, 2)) AS Difference
FROM Payment AS p
WHERE p.[Transaction Type] = 'r'

UNION ALL

SELECT
    m.MaxId + ROW_NUMBER() OVER (ORDER BY r.id) AS id,
    CAST(0 AS decimal(18, 2)) AS Amount,
    r.BookingId,
    CAST(r.Amount AS decimal(18, 2)) AS Difference
FROM Refunds AS r
CROSS JOIN MaxPaymentId AS m;

按照问题中的数据，查询结果类似：

id  Amount  BookingId  Difference
4   8000    3          0
5   10000   4          0
6   0       2          10000

两个查询返回的是不同来源的业务记录，不需要让 SQL Server 去重，因此这里应使用 UNION ALL。

如果需要排除已经执行的退款计划

如果 Refunds 表还会保留已经完成的记录，就需要排除已产生实际退款的项目。只按 BookingId 判断时，可以这样写：

WITH MaxPaymentId AS
(
    SELECT COALESCE(MAX(id), 0) AS MaxId
    FROM Payment
),
PendingRefunds AS
(
    SELECT
        r.id,
        r.Amount,
        r.BookingId
    FROM Refunds AS r
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM Payment AS p
        WHERE p.BookingId = r.BookingId
          AND p.[Transaction Type] = 'r'
    )
)
SELECT
    CAST(p.id AS bigint) AS id,
    p.Amount,
    p.BookingId,
    CAST(0 AS decimal(18, 2)) AS Difference
FROM Payment AS p
WHERE p.[Transaction Type] = 'r'

UNION ALL

SELECT
    m.MaxId + ROW_NUMBER() OVER (ORDER BY r.id),
    CAST(0 AS decimal(18, 2)),
    r.BookingId,
    CAST(r.Amount AS decimal(18, 2))
FROM PendingRefunds AS r
CROSS JOIN MaxPaymentId AS m;

注意事项

通过 MAX(id) + ROW_NUMBER() 生成的 id 只适合展示查询结果。并发插入数据时，这种方式无法保证全局唯一，也不能把生成的 id 写回数据库。更稳妥的方式是分别返回数据来源和原始记录编号：

SELECT
    'Payment' AS SourceType,
    p.id AS SourceId,
    p.Amount,
    p.BookingId,
    CAST(0 AS decimal(18, 2)) AS Difference
FROM Payment AS p
WHERE p.[Transaction Type] = 'r'

UNION ALL

SELECT
    'Refunds',
    r.id,
    CAST(0 AS decimal(18, 2)),
    r.BookingId,
    CAST(r.Amount AS decimal(18, 2))
FROM Refunds AS r;

如果同一个 BookingId 可能发生多次退款或部分退款，只用 BookingId 判断退款是否完成会造成误判。最好在数据库中为退款计划增加状态字段，或者让实际退款记录保存对应的 Refunds.id，以便准确识别每一笔退款计划。

字段名 [Transaction Type] 中含有空格，查询时必须使用方括号。如果可以调整表结构，建议将其改为 TransactionType。