如何调整现有SQL查询获取同时包含所有指定产品的订单列表

• 原SQL存在两处基础语法/逻辑问题：
  • JOIN file f 子句遗漏了关联关键字 ON，直接书写关联条件会触发语法报错
  • 需求明确要求覆盖6款指定产品，但当前WHERE子句的IN列表仅填入5个产品ID，存在漏项
• 现有查询逻辑的本质是匹配“包含任意一款指定产品”的订单，和“必须同时包含全部指定产品”的统计需求不匹配

要筛选出同时包含全部指定产品的订单，核心思路是通过订单维度的聚合校验实现：

1. 按订单ID分组，统计每个订单下出现的指定产品去重数量
2. 仅保留去重产品数和指定产品总数完全一致的订单——这类订单必然没有遗漏任何一款要求的产品
3. 基于筛选出的合法订单ID，关联其余数据表拉取完整的订单明细，避免全表聚合导致的明细数据错位

-- 先筛选符合“包含全部指定产品”要求的订单ID
WITH eligible_orders AS (
    SELECT o.id AS orderid
    FROM orders o
    WHERE o.fk_product_id IN ('66','8','6','21','11', '/* 补全第6款产品的ID */')
    GROUP BY o.id
    -- 统计去重后的指定产品数，和要求的6款总数完全匹配即为合格订单
    HAVING COUNT(DISTINCT o.fk_product_id) = 6
)
-- 关联全量表取订单完整明细
SELECT 
    o.order_date,
    o.price AS revenue,
    o.cost,
    f.id AS fileid,
    o.id AS orderid,
    o.fk_order_status_id,
    o.date_started,
    o.date_completed,
    p.description AS product,
    pp.description AS subproduct
FROM orders o
-- 修复原查询缺失的ON关键字
JOIN file f 
    ON f.id = o.fk_file_id
JOIN product p 
    ON p.id = o.fk_product_id
JOIN product_product pp 
    ON pp.fk_product_id = o.fk_product_id
-- 仅保留筛选通过的订单
JOIN eligible_orders eo
    ON eo.orderid = o.id
-- 限定拉取的明细仅属于指定产品范围，避免带出订单内其他无关产品数据
WHERE o.fk_product_id IN ('66','8','6','21','11', '/* 同上补全第6款产品ID */')

• 聚合统计时必须加DISTINCT关键字，避免因为关联product_product表产生的多行重复记录，导致产品数统计虚高、误判合格订单
• 如果仅需做合格订单的计数统计，无需拉取明细，可直接在eligible_orders结果上做COUNT(*)即可，不需要关联其余业务表，查询效率会更高

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