MySQL使用派生表按产品和月份分组统计销量（含零值）

Here's the complete query that fits your constraints (no CTEs, temp tables—only derived tables) and produces the exact expected result:

SELECT 
    p.product,
    m.mn AS month,
    COUNT(t.id) AS how_many
FROM 
    -- Derived table to fetch all unique products from sales data
    (SELECT DISTINCT product FROM t) AS p
-- Cross join to generate every possible product-month combination
CROSS JOIN 
    (SELECT 1 AS mn UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 
     UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) AS m
-- Left join to match actual sales records to the product-month pairs
LEFT JOIN 
    t ON p.product = t.product AND m.mn = t.sold_on_month
-- Group by product and month to calculate sales counts
GROUP BY 
    p.product, m.mn
-- Sort results to match the expected output order
ORDER BY 
    p.product, m.mn;

Step-by-Step Explanation: #

• Unique Products Derived Table: (SELECT DISTINCT product FROM t) AS p captures every distinct product that has ever had sales, ensuring we don't miss any product in the final result.
• Months Derived Table: This is the same 1–12 month list you already built—it provides a full set of months to pair with each product.
• Cross Join: By cross joining the product list and month list, we create a complete set of product-month pairs. This is the key step that fills in the gaps for months where a product had no sales.
• Left Join to Sales Data: We link this full product-month set back to your original sales table t using both product and month. This preserves all product-month pairs even if there are no matching sales entries.
• Count Sales Records: COUNT(t.id) counts valid sales entries. Since left-joined rows with no sales will have NULL values for t.id, COUNT() ignores these and returns 0 for those months.
• Group & Sort: Grouping by product and month ensures we calculate counts per product-month, and sorting keeps the output organized exactly as you specified.

This query will return your desired result, showing 0 for any month a product had no sales, all using only derived tables as required.

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