如何修改SQL查询移除应收款已结清的客户参考行

原始客户采购活动表格 #

原始查询语句 #

Select DateOfActivity, CustomerReferencce, ReferenceLine, Description, ReceivableAmount
From 'Table A'
Where DateOfActivity >= '2022-09-01'
Group by DateOfActivity

预期查询结果 #

说明：CUST567 Reference Line 4的所有记录已被移除，因为该组合下应收款总和为0，其他客户记录均保留。

修改后的查询语句（适配大数据场景） #

WITH CustomerBalances AS (
    SELECT 
        CustomerReference, 
        ReferenceLine,
        SUM(CAST(REPLACE(ReceivableAmount, ',', '') AS DECIMAL(18,2))) AS TotalReceivable
    FROM 'Table A'
    WHERE DateOfActivity >= '2022-09-01'
    GROUP BY CustomerReference, ReferenceLine
)
SELECT 
    t.DateOfActivity, 
    t.CustomerReference, 
    t.ReferenceLine, 
    t.Description, 
    t.ReceivableAmount
FROM 'Table A' t
JOIN CustomerBalances cb 
    ON t.CustomerReference = cb.CustomerReference 
    AND t.ReferenceLine = cb.ReferenceLine
WHERE 
    t.DateOfActivity >= '2022-09-01'
    AND cb.TotalReceivable != 0
ORDER BY t.DateOfActivity, t.CustomerReference, t.ReferenceLine;

核心逻辑说明 #

1. 预计算余额：通过CTECustomerBalances一次性计算每个CustomerReference+ReferenceLine组合的应收款总和，处理金额字段中的逗号并转换为数值类型，确保求和准确。
2. 过滤结清记录：将原始表与预计算的余额表关联，直接过滤掉总和为0的客户+行号组合，避免重复计算。
3. 大数据适配：分组计算仅执行一次，联合索引（建议在CustomerReference和ReferenceLine上创建）可大幅提升JOIN操作效率，适配数据持续增长的场景。

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