MySQL 5.7中筛选支付表未完全抵消的AJUSTE与ESTORNO记录

核心思路 #

用条件聚合按invoice_no分组，分别计算调整项（AJUSTE）的总和、退款项（ESTORNO）总和的绝对值，再根据两者的大小关系筛选并生成最终结果：

1. 先过滤出2024年、active='1'的有效记录
2. 分组计算两类金额的总和
3. 排除完全抵消的发票，对剩余发票计算差额并匹配对应支付类型

完整SQL语句 #

SELECT
    invoice_no,
    CASE
        WHEN ajuste_total > estorno_total_abs THEN 'AJUSTE'
        ELSE 'ESTORNO'
    END AS payment_type,
    ABS(ajuste_total - estorno_total_abs) AS difference
FROM (
    SELECT
        invoice_no,
        SUM(CASE WHEN type = 'AJUSTE' THEN amount ELSE 0 END) AS ajuste_total,
        SUM(CASE WHEN type = 'ESTORNO' THEN ABS(amount) ELSE 0 END) AS estorno_total_abs
    FROM payments
    WHERE
        YEAR(payment_date) = 2024
        AND active = '1'
    GROUP BY invoice_no
) AS grouped_data
WHERE ajuste_total != estorno_total_abs
ORDER BY invoice_no;

语句说明 #

1. 子查询分组计算：
   • ajuste_total：统计当前发票下所有AJUSTE类型的金额总和（AJUSTE本身为正数，直接求和即可）
   • estorno_total_abs：统计当前发票下所有ESTORNO类型金额的绝对值总和（ESTORNO为负数，用ABS(amount)转换为正数后求和）
2. 外层筛选与结果生成：
   • 用WHERE ajuste_total != estorno_total_abs排除完全抵消的发票
   • 通过CASE判断哪类金额总和更大，输出对应的支付类型
   • 用ABS(ajuste_total - estorno_total_abs)计算正数形式的差额
3. 兼容性：完全适配MySQL 5.7版本，未使用高版本专属特性

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