如何在MySQL中不使用DISTINCT、GROUP BY和OVER()关键字实现表去重查询，并关联四张表获取订单唯一行及总价？

Great question! It's tricky when you're restricted from using the usual deduplication tools like DISTINCT, GROUP BY, or window functions with OVER(). Let's break this down and find a solution that fits your constraints.

The Core Problem #

Your original query returns duplicate rows because you're joining directly against the full Order_details table—each row in Order_details creates a separate result row for the same order. We need to first filter Order_details to get one unique row per Order_id, then join that with your other tables.

Approach: Use NOT EXISTS to Select Unique Order Rows #

We can use a correlated subquery with NOT EXISTS to pick a single row per Order_id. The idea is to select only the "first" row for each order (we'll use the smallest Good_id as our marker for "first"—you could adjust this to pick the largest or any other criteria if needed).

Here's the full query that solves your problem:

SELECT 
    od_unique.Order_id,
    Orders.Date,
    People.First_name,
    People.Surname,
    (
        -- Calculate total price for the order (unchanged from your logic, but now runs once per order)
        SELECT SUM(g.Price * od_inner.Quantity)
        FROM Order_details od_inner
        JOIN Goods g ON od_inner.Good_id = g.Good_id
        WHERE od_inner.Order_id = od_unique.Order_id
    ) AS Total_price
FROM (
    -- Subquery to get one unique row per Order_id
    SELECT od1.Order_id, od1.Good_id, od1.Quantity
    FROM Order_details od1
    WHERE NOT EXISTS (
        -- Check if there's another row in Order_details for the same order with a smaller Good_id
        SELECT 1
        FROM Order_details od2
        WHERE od2.Order_id = od1.Order_id
        AND od2.Good_id < od1.Good_id
    )
) od_unique
-- Join with other tables as needed
JOIN Orders ON od_unique.Order_id = Orders.Order_id
JOIN People ON Orders.Person_id = People.Person_id
ORDER BY od_unique.Order_id ASC;

How This Works #

1. Deduplication with NOT EXISTS: The inner subquery od_unique filters Order_details to keep only rows where there's no other row for the same Order_id with a smaller Good_id. This ensures each Order_id appears exactly once in od_unique.
2. Clean Joins: By joining against the deduplicated od_unique instead of the full Order_details table, we avoid duplicate rows in the final result.
3. Total Price Calculation: The subquery for Total_price still calculates the sum for the entire order, but since each order only appears once in the main query, this sum is computed once per order (not per order item).

Alternative: Using MIN(Good_id) in a Correlated Subquery #

If you prefer a slightly different syntax, you can also filter rows by matching the smallest Good_id per order:

SELECT 
    od.Order_id,
    Orders.Date,
    People.First_name,
    People.Surname,
    (
        SELECT SUM(g.Price * od_inner.Quantity)
        FROM Order_details od_inner
        JOIN Goods g ON od_inner.Good_id = g.Good_id
        WHERE od_inner.Order_id = od.Order_id
    ) AS Total_price
FROM Order_details od
JOIN Orders ON od.Order_id = Orders.Order_id
JOIN People ON Orders.Person_id = People.Person_id
-- Keep only the row with the smallest Good_id for each order
WHERE od.Good_id = (
    SELECT MIN(Good_id) FROM Order_details WHERE Order_id = od.Order_id
)
ORDER BY od.Order_id ASC;

This achieves the same deduplication effect without using any of the restricted keywords.

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