技术需求：查询含重复产品名称的订单（示例结果：order_id 10）

Hey there, let's tackle this problem of finding orders that contain duplicate product names. Looking at your sample data, order 10 is the one we need to target (since it has two products both named "potato"). Here are a couple of solid SQL approaches to get this done:

1. Using GROUP BY + HAVING (Simple & Direct) #

This method groups records by order ID and product name, then filters for groups where the same product name appears more than once in a single order:

SELECT o.order_id
FROM Orders o
JOIN Order_details od ON o.order_id = od.order_id
JOIN Products p ON od.product_id = p.product_id
GROUP BY o.order_id, p.product_name
HAVING COUNT(*) > 1;

How it works: #

• We first join all three tables to link each order to its associated product names.
• GROUP BY o.order_id, p.product_name groups every unique combination of order and product name.
• HAVING COUNT(*) > 1 keeps only those groups where the same product name shows up multiple times in the same order.

2. Using Window Functions (For More Context) #

If you want to see the actual duplicate product entries alongside the order ID, a window function approach gives you more flexibility:

WITH OrderProductCounts AS (
    SELECT 
        o.order_id,
        p.product_name,
        -- Count how many times each product name appears in the order
        COUNT(*) OVER(PARTITION BY o.order_id, p.product_name) AS name_occurrences
    FROM Orders o
    JOIN Order_details od ON o.order_id = od.order_id
    JOIN Products p ON od.product_id = p.product_id
)
-- Get distinct order IDs where any product name repeats
SELECT DISTINCT order_id
FROM OrderProductCounts
WHERE name_occurrences > 1;

How it works: #

• The CTE OrderProductCounts calculates the number of times each product name appears in every order using COUNT() OVER().
• We then filter for rows where the count is greater than 1, and use DISTINCT to avoid duplicate order IDs in the result.

Both queries will return order_id 10 as expected with your sample data.

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