SQL按order_number分组查完全删除订单 存在date_deleted为null就返回null的方法

原SQL错误原因 #

你写的SQL首先在WHERE子句过滤了date_deleted IS NOT NULL的行，直接把同订单下未删除的商品（date_deleted为null）排除在了分组统计范围外，分组时无法感知到还有未删除的商品，自然得到错误结果。

实现需求的正确SQL #

你可以在分组后统计组内date_deleted为null的记录数来判断返回值，全数据库兼容的写法如下：

SELECT 
  order_number,
  CASE 
    WHEN COUNT(CASE WHEN date_deleted IS NULL THEN 1 END) > 0 THEN NULL
    ELSE MAX(date_deleted) 
  END AS date_deleted
FROM orders
GROUP BY order_number

如果使用PostgreSQL/MySQL这类支持逻辑聚合函数的数据库，写法可以更简化：

-- PostgreSQL 版本
SELECT 
  order_number,
  CASE WHEN BOOL_OR(date_deleted IS NULL) THEN NULL ELSE MAX(date_deleted) END AS date_deleted
FROM orders
GROUP BY order_number

仅筛选整单完全删除的订单 #

如果最终只需要拿到完全删除的订单列表，直接在HAVING子句加过滤条件即可：

SELECT order_number
FROM orders
GROUP BY order_number
HAVING COUNT(CASE WHEN date_deleted IS NULL THEN 1 END) = 0

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