请求协助执行复杂SQL查询：计算物品库存出入总量

Got it, let's work through this SQL problem together. The goal is to calculate total stock-in and stock-out quantities for each item, and we can achieve this with a conditional aggregation approach that joins all three tables properly.

Step-by-Step Explanation #

1. Join the Tables: We need to link ITEM to INVENTORY (via Item_id), and INVENTORY to STATUS (via Status_id). Using LEFT JOIN ensures we don't miss items that might not have inventory records (though your sample data has all items covered).
2. Group by Item: We'll group our results by each item's ID and name to aggregate quantities per item.
3. Conditional SUM: Use CASE WHEN inside SUM() to calculate total stock-in and stock-out separately based on the status name or ID.

Final SQL Query #

SELECT
    i.Name AS Item,
    SUM(CASE WHEN s.Stat_Name = 'Stock In' THEN inv.Quantity ELSE 0 END) AS overall_stock_in,
    SUM(CASE WHEN s.Stat_Name = 'Stock Out' THEN inv.Quantity ELSE 0 END) AS overall_stock_out
FROM
    ITEM i
LEFT JOIN
    INVENTORY inv ON i.id = inv.Item_id
LEFT JOIN
    STATUS s ON inv.Status_id = s.id
GROUP BY
    i.id, i.Name
ORDER BY
    i.Name;

Why This Works #

• Conditional SUM: For each row in INVENTORY, we check if the status is "Stock In" or "Stock Out". If it matches, we include the quantity in the sum; otherwise, we add 0.
• Grouping: By grouping on ITEM.id and ITEM.Name, we ensure each item gets a single row with its total aggregated values.
• Joins: The joins connect all the necessary data so we can link each inventory record to its corresponding item and status.

Test Result with Your Sample Data #

Running this query against your provided tables will return exactly the output you need:

Item       overall_stock_in  overall_stock_out
Computer1  130               200
Computer2  35                36

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