MySQL中按SKU统计下单量、退货量及退货占比的查询异常排查

The Problem with Your Query #

The inflated Ordered count happens because when you LEFT JOIN Orders with RMA, any order that has multiple RMA entries gets duplicated once per RMA. Using count(SKU) counts all these duplicate rows, which makes the Ordered value larger than the actual number of unique orders for that SKU.

Fixed Query Using DISTINCT #

To fix this, use COUNT(DISTINCT Orders.OrderID) to count unique orders per SKU, and adjust the percentage calculation to use this correct order count as the denominator:

SELECT 
    SKU,
    Description,
    COUNT(DISTINCT Orders.OrderID) AS Ordered, 
    COUNT(RMA.OrderID) AS Returned,
    ROUND(100 * COUNT(RMA.OrderID) / COUNT(DISTINCT Orders.OrderID)) AS Percent_Returned
FROM Orders
LEFT JOIN RMA USING (OrderID)
GROUP BY SKU, Description
ORDER BY Percent_Returned DESC;

Alternative Approach: Subqueries (More Explicit) #

If you prefer a more readable approach, you can calculate order counts and return counts separately in subqueries, then join them. This avoids duplication issues entirely:

SELECT 
    o.SKU,
    o.Description,
    o.Ordered,
    COALESCE(r.Returned, 0) AS Returned, -- Handle SKUs with no returns
    ROUND(100 * COALESCE(r.Returned, 0) / o.Ordered) AS Percent_Returned
FROM (
    -- Get total orders per SKU directly from Orders table
    SELECT SKU, Description, COUNT(OrderID) AS Ordered
    FROM Orders
    GROUP BY SKU, Description
) o
LEFT JOIN (
    -- Get total returns per SKU (count of RMA entries)
    SELECT Orders.SKU, COUNT(RMA.RMAID) AS Returned
    FROM Orders
    JOIN RMA USING (OrderID)
    GROUP BY Orders.SKU
) r ON o.SKU = r.SKU
ORDER BY Percent_Returned DESC;

Why This Works #

• The COUNT(DISTINCT Orders.OrderID) in the first query ensures each order is counted only once, even if it has multiple RMAs.
• The subquery approach calculates order counts directly from the Orders table (no joins, no duplicates) and return counts from the joined Orders and RMA tables, then combines the results cleanly.
• COALESCE ensures SKUs with no returns show 0 instead of NULL for the Returned column.

Testing either query should give you the correct Ordered counts matching your earlier query results, along with the accurate return counts and percentages you need.

内容的提问来源于stack exchange，提问作者Jeff M.