SQL查询：校验用户同一商品负交易是否有对应正交易及余额合规性

数据老陈

2026-9-26

解决方案

校验逻辑梳理

我们要排查的不合规场景满足以下任意一个条件即可判定：

同一number_id+item_id组合存在负金额交易，但不存在对应正金额交易
同一number_id+item_id组合的所有交易总余额小于0

SQL实现

查询不合规的用户商品组合及统计数据

SELECT 
    number_id,
    item_id,
    SUM(amount) AS total_balance,
    MAX(CASE WHEN amount < 0 THEN 1 ELSE 0 END) AS has_negative_trans,
    MAX(CASE WHEN amount > 0 THEN 1 ELSE 0 END) AS has_positive_trans
FROM TBL_A
GROUP BY number_id, item_id
HAVING 
    (MAX(CASE WHEN amount < 0 THEN 1 ELSE 0 END) = 1 AND MAX(CASE WHEN amount > 0 THEN 1 ELSE 0 END) = 0)
    OR SUM(amount) < 0;

查询不合规组合对应的所有原始交易记录

如果需要导出不合规组合对应的全部交易明细，可以用关联查询实现：

SELECT a.*
FROM TBL_A a
INNER JOIN (
    SELECT number_id, item_id
    FROM TBL_A
    GROUP BY number_id, item_id
    HAVING 
        (MAX(CASE WHEN amount < 0 THEN 1 ELSE 0 END) = 1 AND MAX(CASE WHEN amount > 0 THEN 1 ELSE 0 END) = 0)
        OR SUM(amount) < 0
) invalid_group 
ON a.number_id = invalid_group.number_id AND a.item_id = invalid_group.item_id;