优化含不兼容产品的订单查询SQL逻辑需求

First, let's clarify the core requirement: we need to find orders that contain either of these valid cross-combinations:

• Baseball Bats + Cricket Balls
• Cricket Bats + Baseball Balls

What Was Wrong with the Original Query? #

The original approach had two critical flaws:

1. Only targets one valid combination: It filters for Cricket Bats or Baseball Balls, so it completely misses orders with Baseball Bats and Cricket Balls.
2. Count-based check is unreliable: Using COUNT >=2 can incorrectly include orders with multiple copies of the same item (e.g., two Cricket Bats with no Baseball Balls) while excluding valid orders that might have additional unrelated items.

Corrected SQL Query #

This version uses conditional aggregation to explicitly check for the presence of both required items in each order, regardless of other items present:

SELECT llo.OrderNbr
FROM LineLevelOrders llo
JOIN ItemInfo ii ON llo.SKU = ii.SKU
WHERE ii.Type IN ('Bats', 'Balls') -- Focus only on relevant product types
GROUP BY llo.OrderNbr
HAVING 
    -- Check for Baseball Bats + Cricket Balls
    (MAX(CASE WHEN ii.Type = 'Bats' AND ii.Range = 'Baseball' THEN 1 ELSE 0 END) = 1
     AND MAX(CASE WHEN ii.Type = 'Balls' AND ii.Range = 'Cricket' THEN 1 ELSE 0 END) = 1)
    OR
    -- Check for Cricket Bats + Baseball Balls
    (MAX(CASE WHEN ii.Type = 'Bats' AND ii.Range = 'Cricket' THEN 1 ELSE 0 END) = 1
     AND MAX(CASE WHEN ii.Type = 'Balls' AND ii.Range = 'Baseball' THEN 1 ELSE 0 END) = 1);

How This Works #

• Conditional Aggregation: Each MAX(CASE...) checks if at least one item in the order matches the specified Type/Range combination. If yes, it returns 1; otherwise 0.
• HAVING Clause: We combine these checks with AND to ensure both parts of a valid pair exist in the order, then use OR to include both valid cross-combinations.
• JOIN Instead of LEFT JOIN: We only care about line items with valid product info (since we need Type/Range data), so an inner join excludes any line items without matching SKUs in ItemInfo.

This approach correctly identifies all valid orders, even if they contain additional unrelated items, and avoids false positives from duplicate items of the same type.

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