sql-server - SQL Server 从 2 个表中选择项目总和

Question

我有 2 张桌子；入站和出站。它们都具有相同的结构。

• TransactionId (int),
• 日期（日期时间），
• StoreItemId (int) [FK],
• 数量（实际）

我尝试了这个 select 语句，结果如下图所示：

SELECT [StoreItemId], SUM([Quantity]) AS 'inbound' FROM [Inbound] GROUP BY [StoreItemId];
SELECT [StoreItemId], SUM([Quantity]) AS 'outbound' FROM [Outbound] GROUP BY [StoreItemId];

我尝试了一个查询：

SELECT
    (SELECT SUM([Quantity]) AS 'inbound' FROM [Inbound] WHERE [Inbound].[StoreItemId] = 1) - 
    (SELECT SUM([Quantity]) AS 'Outbound' FROM [Outbound] WHERE [Outbound].[StoreItemId] = 1)

结果为 105（115-10 ；StoreItemId 的入库数量 = 1 - StoreItemId 的出库数量 = 1）

我尝试将上面的两个查询组合成这样的查询：

SELECT [StoreItemId] AS 'xyz'
       (SELECT SUM([Quantity]) FROM [Inbound] WHERE [Inbound].[StoreItemId] = [xyz]) - 
       (SELECT SUM([Quantity]) FROM [Outbound] WHERE [Outbound].[StoreItemId] = [xyz])
FROM [StoreItem]

但这是错误的

结果应该是：

StoreItemId | Balance
----------------------
1           |   105
2           |   126
3           |   78
4           |   144
5           |   100
6           |   179

标签： sql-serverselect

Answer 1

您可以使用LEFT JOIN与GROUP BYAND SUMof QUANTITY。您可以参考下面的查询。

select StoreItemId  , (sum(ISNULL(inb.Quantity,0)) - sum(ISNULL(ob.Qunatity,0))) as Balance
from Inbound inb 
LEFT JOIN Outbound ob on ob.StoreItemId = si.StoreItemId
GROUP BY StoreItemId