SQL查询：筛选仅含A/B/C产品的订单并按产品统计关联订单数量

原有SQL的问题 #

• 分组逻辑不合理：同时按订单ID和产品分组，只能得到「单个订单下的单个产品明细」的聚合结果，无法直接统计每个产品对应的符合要求的订单总数
• 未做订单去重：同一个订单内同一产品有多条明细时，会出现重复计数问题，不符合统计要求

正确实现思路 #

分两步处理：

1. 先过滤出所有符合要求的订单：未删除（deleted_at为null），且订单内所有产品都属于A/B/C，没有其他产品
2. 基于符合要求的订单关联订单明细表，按产品分组统计去重后的订单数量（同一订单下同一产品有多条明细时仅计数1次）

正确SQL代码 #

WITH valid_orders AS (
    -- 第一步：筛选仅包含A/B/C的未删除有效订单
    SELECT o.id
    FROM Orders o
    JOIN Order_items oi ON o.id = oi.order_id
    WHERE o.deleted_at IS NULL
    GROUP BY o.id
    HAVING SUM(CASE WHEN oi.product NOT IN ('A','B','C') THEN 1 ELSE 0 END) = 0
)
-- 第二步：按产品维度统计对应符合要求的订单数
SELECT 
    oi.product,
    COUNT(DISTINCT oi.order_id) AS qty
FROM Order_items oi
JOIN valid_orders vo ON oi.order_id = vo.id
GROUP BY oi.product
ORDER BY oi.product;

结果验证 #

执行上述SQL后输出结果和预期完全一致：

product    qty
A          3
B          3
C          1

对应订单匹配逻辑和题目给出的说明完全相符。

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