SQL查询：存在指定订单项但缺失必填关联订单项的订单

核心思路 #

要找出包含Product ID为"abc"且Discount Type为"p"的订单项，但未包含Product ID为"ppp"订单项的异常订单，可通过两步逻辑实现：

1. 先筛选出所有触发规则的订单（即存在"abc"+"p"订单项的订单）
2. 从这些订单中排除已包含"ppp"订单项的订单，剩余即为异常订单

基础查询实现 #

方式1：使用NOT EXISTS子查询（性能较优）

-- 直接获取异常订单ID
SELECT DISTINCT oli.order_id
FROM order_line_items oli
WHERE oli.product_id = 'abc' 
  AND oli.discount_type = 'p'
  AND NOT EXISTS (
      -- 检查当前订单是否存在ppp订单项
      SELECT 1
      FROM order_line_items oli2
      WHERE oli2.order_id = oli.order_id
        AND oli2.product_id = 'ppp'
  )

方式2：使用左连接筛选

-- 先获取触发规则的订单，再左连接排除已包含ppp的订单
SELECT DISTINCT abc_orders.order_id
FROM (
    -- 筛选存在abc+p的订单
    SELECT order_id
    FROM order_line_items
    WHERE product_id = 'abc' AND discount_type = 'p'
) abc_orders
LEFT JOIN (
    -- 筛选存在ppp的订单
    SELECT order_id
    FROM order_line_items
    WHERE product_id = 'ppp'
) ppp_orders ON abc_orders.order_id = ppp_orders.order_id
-- 左连接后ppp订单ID为空的就是缺失的异常订单
WHERE ppp_orders.order_id IS NULL

关联订单头表获取完整信息 #

如果需要查询订单的完整详情（如订单日期、客户信息等），可直接关联order_header表：

SELECT oh.*
FROM order_header oh
JOIN (
    -- 复用异常订单判断逻辑
    SELECT DISTINCT oli.order_id
    FROM order_line_items oli
    WHERE oli.product_id = 'abc' 
      AND oli.discount_type = 'p'
      AND NOT EXISTS (
          SELECT 1
          FROM order_line_items oli2
          WHERE oli2.order_id = oli.order_id
            AND oli2.product_id = 'ppp'
      )
) abnormal_orders ON oh.order_id = abnormal_orders.order_id

扩展说明 #

• 针对其他Discount Type对应其他必填产品的规则，可直接修改上述查询的条件；若规则较多，建议维护一张规则映射表（如discount_rule(product_id, discount_type, required_product_id)），通过关联该表实现批量规则查询
• 该查询会自动忽略不包含"abc"订单项的订单，因为第一步仅筛选满足触发条件的订单集合

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