如何编写含多SUM多WHERE的MySQL语句整合商品与销售表查询

Got it, let's tackle this problem step by step. Instead of running separate queries for each sales metric, we can use conditional aggregation (pairing SUM() with CASE WHEN) to calculate all your required values in one go, while joining your items and invoiceLines tables to pull in product details and inventory.

Here's a complete example tailored to your tables: #

SELECT
    i.id,
    i.itemDesc,
    i.qtyOnHandW1,
    -- Total shipped in November 2023 (adjust date range as needed)
    ROUND(SUM(CASE WHEN il.invDate BETWEEN '2023-11-01' AND '2023-11-30' THEN il.qtyShipped ELSE 0 END)) AS m11,
    -- Total shipped in December 2023
    ROUND(SUM(CASE WHEN il.invDate BETWEEN '2023-12-01' AND '2023-12-31' THEN il.qtyShipped ELSE 0 END)) AS m12,
    -- Total shipped in the last 7 days
    ROUND(SUM(CASE WHEN il.invDate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN il.qtyShipped ELSE 0 END)) AS last_week_sales
FROM
    items i
LEFT JOIN
    invoiceLines il ON i.id = il.itemCode -- Assumes itemCode in invoiceLines maps to id in items
GROUP BY
    i.id, i.itemDesc, i.qtyOnHandW1;

How this works: #

• Table Join: We use LEFT JOIN to ensure every product from items appears in the results, even if it has no sales history (those will show 0 for the SUM columns). If you only want products with sales, swap this for INNER JOIN.
• Conditional SUMs: Each SUM(CASE WHEN ...) acts like a filtered sum—only rows that match the invDate condition contribute to the total. The ELSE 0 ensures missing sales don't return NULL.
• Grouping: We group by all non-aggregated columns from items to ensure each row represents a single product with its inventory and all calculated sales metrics.

Customization Tips: #

• Adjust the CASE WHEN conditions to match your original query filters (e.g., add additional criteria like specific regions or order statuses if needed).
• If you need to filter the entire dataset (e.g., only include active products), add a WHERE clause before GROUP BY (e.g., WHERE i.itemDesc NOT LIKE '%Discontinued%').
• Double-check the join condition (i.id = il.itemCode) to make sure it correctly links your product IDs between the two tables.

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