如何合并Patient表的SQL求和查询并处理payment_amount抵消场景

嘿，我来帮你搞定这个问题！咱们先把三个业务场景的核心逻辑拆解清楚，再把它们整合成一个能覆盖所有情况的SQL查询：

核心需求回顾 #

• 场景1（账户999888）：仅统计payment_amount != 0的记录的payment_amount2总和
• 场景2（账户991991）：当账户所有记录的payment_amount都是0时，统计所有payment_amount2的总和
• 场景3（账户989111）：如果某笔payment_amount存在等额反向的抵消记录（比如44和-44），这两条记录的payment_amount2都不计入求和

整合后的SQL查询 #

WITH payment_groups AS (
    -- 第一步：按账户、NRV、金额绝对值分组，判断是否存在抵消
    SELECT
        account_number,
        nrv,
        ABS(payment_amount) AS abs_pay,
        SUM(payment_amount) AS total_pay
    FROM patient
    GROUP BY account_number, nrv, ABS(payment_amount)
),
account_pay_status AS (
    -- 第二步：判断每个账户+NRV组合是否存在非0的payment_amount记录
    SELECT
        account_number,
        nrv,
        CASE WHEN EXISTS (
            SELECT 1 FROM patient p 
            WHERE p.account_number = aps.account_number 
              AND p.nrv = aps.nrv 
              AND p.payment_amount != 0
        ) THEN 1 ELSE 0 END AS has_non_zero_pay
    FROM (SELECT DISTINCT account_number, nrv FROM patient) aps
)
SELECT
    p.account_number,
    p.nrv,
    SUM(
        CASE
            -- 排除被抵消的记录
            WHEN pg.total_pay = 0 THEN 0
            -- 有非0记录时，只统计payment_amount≠0的
            WHEN aps.has_non_zero_pay = 1 AND p.payment_amount = 0 THEN 0
            -- 其他情况（全0账户或非0且未抵消的记录）统计payment_amount2
            ELSE p.payment_amount2
        END
    ) AS payment_amount2_total
FROM patient p
JOIN account_pay_status aps 
    ON p.account_number = aps.account_number 
    AND p.nrv = aps.nrv
LEFT JOIN payment_groups pg 
    ON p.account_number = pg.account_number 
    AND p.nrv = pg.nrv 
    AND ABS(p.payment_amount) = pg.abs_pay
GROUP BY p.account_number, p.nrv;

代码逻辑解释 #

1. payment_groups CTE：

   • 按账户、NRV、payment_amount的绝对值分组，计算每组的payment_amount总和
   • 如果总和为0，说明这组存在等额反向抵消，对应的payment_amount2要排除

2. account_pay_status CTE：

   • 标记每个账户+NRV组合是否存在payment_amount != 0的记录
   • 用来区分“有非0记录的账户”和“全0记录的账户”

3. 主查询：

   • 通过CASE语句整合三个场景的逻辑：
     • 先排除被抵消的记录（pg.total_pay = 0时，payment_amount2计为0）
     • 对于有非0记录的账户，排除payment_amount = 0的记录
     • 剩下的情况（全0账户或非0且未抵消的记录），正常计入payment_amount2

测试验证 #

把你给出的三个测试数据代入这个查询，就能得到符合预期的结果：

• 账户999888：只统计payment_amount为99和44的记录，总和是100+200=300
• 账户991991：所有记录都是0，总和是150
• 账户989111：排除payment_amount为44和-44的记录，总和是100+150=250

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