如何编写SQL查询校验存在负值的number_id、item_id组合必有正值

方案1：分组聚合查询（最直观） #

直接按number_id、item_id分组，统计是否存在负值和正值，过滤出存在负值但不存在正值的异常组合：

SELECT 
    number_id,
    item_id,
    CASE 
        WHEN SUM(CASE WHEN amount < 0 THEN 1 ELSE 0 END) > 0 
             AND SUM(CASE WHEN amount > 0 THEN 1 ELSE 0 END) = 0 
        THEN '异常：存在负值但无对应正值'
        ELSE '正常'
    END AS check_result
FROM TBL_A
GROUP BY number_id, item_id
-- 如果只需要看异常组合，打开下面的注释
-- HAVING SUM(CASE WHEN amount < 0 THEN 1 ELSE 0 END) > 0 
--    AND SUM(CASE WHEN amount > 0 THEN 1 ELSE 0 END) = 0

逻辑说明 #

• 用SUM(CASE WHEN amount <0 THEN 1 ELSE 0 END)统计当前组合下负值的记录数，大于0说明存在负值
• 用SUM(CASE WHEN amount >0 THEN 1 ELSE 0 END)统计当前组合下正值的记录数，等于0说明不存在正值
• 两个条件同时满足就是不符合规则的异常组合

方案2：EXISTS关联查询 #

如果需要关联明细数据可以用这种写法，查询返回的所有结果都是不符合规则的异常组合，无返回结果说明所有组合都符合校验规则：

SELECT DISTINCT t1.number_id, t1.item_id
FROM TBL_A t1
WHERE t1.amount < 0
  AND NOT EXISTS (
    SELECT 1 
    FROM TBL_A t2 
    WHERE t2.number_id = t1.number_id 
      AND t2.item_id = t1.item_id 
      AND t2.amount > 0
)

测试数据运行结果 #

你提供的测试数据中所有带负值的number_id+item_id组合都存在对应正值，仅number_id=121200的组合只有正值属于正常情况，因此上述两个查询都不会返回异常记录。

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