基于公共列条件查找多行：根据指定商品价格映射列表查询订单号

Alright, let's figure out how to pull the shared OrderNos that include your exact (item, price) pairs. Here's a step-by-step breakdown:

Your Raw Data #

First, let's lay out your order table clearly:

The Issue with Your Initial Query #

Your starting statement Select OrderNo from table tl where item in ('itemB' ,'itemD') and price in ('20','15') GROUP ... has a key flaw: it doesn't enforce the exact pairing of item and price. For example, if there was a row with itemB + 15 or itemD + 20, it would still get pulled in, which isn't what you want. We need to make sure we're matching the specific (item, price) pairs together.

Working SQL Solutions #

Method 1: GROUP BY + HAVING (Best for Fixed Pair Counts) #

This approach groups by OrderNo and verifies all required pairs are present:

SELECT OrderNo
FROM your_table_name
WHERE (item = 'itemB' AND price = 20) 
   OR (item = 'itemD' AND price = 15)
GROUP BY OrderNo
HAVING COUNT(DISTINCT CONCAT(item, '-', price)) = 2; -- 2 matches the number of pairs you're checking

How this works:

1. We first filter rows to only include your two target (item, price) pairs.
2. Group the results by OrderNo.
3. Use COUNT(DISTINCT ...) to ensure the order has both unique pairs (the concat ensures we don't count duplicate same pairs in one order as multiple entries).

From your data, this will return OrderNo = 4—the only order that has both itemB + 20 and itemD + 15.

Method 2: Self-Join (Great for Complex Pair Scenarios) #

If you have more pairs or need more flexibility, a self-join works well:

SELECT DISTINCT t1.OrderNo
FROM your_table_name t1
JOIN your_table_name t2 ON t1.OrderNo = t2.OrderNo
WHERE t1.item = 'itemB' AND t1.price = 20
  AND t2.item = 'itemD' AND t2.price = 15;

How this works:

We join the table to itself on OrderNo, then check that one side has your first pair and the other has your second pair. DISTINCT ensures we only get each OrderNo once.

Method 3: EXISTS Subqueries (High Readability) #

If you want super clear logic that scales easily for more pairs, use EXISTS checks:

SELECT DISTINCT OrderNo
FROM your_table_name t
WHERE EXISTS (
    SELECT 1 FROM your_table_name 
    WHERE OrderNo = t.OrderNo AND item = 'itemB' AND price = 20
)
AND EXISTS (
    SELECT 1 FROM your_table_name 
    WHERE OrderNo = t.OrderNo AND item = 'itemD' AND price = 15
);

How this works:

We check each OrderNo individually: first that it has the itemB + 20 pair, then that it also has the itemD + 15 pair. Only orders that pass both checks get selected.

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