从具有相同ID的不同表中减去2列

本文关键字：2列 ID | 更新日期: 2023-09-27 18:02:39

我有两个表TblAddToInventory和TblWithdrawnFromInventory。都有ProductID和Quantity。提款时，自然应扣除项目数量，但只扣除已提款的项目。例子:

TblAddToInventory 

ProductID | Quantity | Amount | Date
1           2          2.00     7/7/2012
2           3          3.00     7/7/2012
3           4          4.00     7/7/2012
2           2          2.00     7/8/2012
3           3          3.00     7/8/2012

TblWithdrawnFromInventory 

ProductID | Quantity | Amount | Date
2           4          4.00     7/9/2012
3           5          5.00     7/10/2012

有了这个，当我连接两个表并扣除特定列时，我应该有一个DataGridView使用c#与这些数据:

ProductID | Quantity | Amount 
1           2          2.00
2           1          1.00
3           2          2.00

我知道如何使用SUM和JOIN，但我只是不知道如何创建一种语法，将从具有相同ID的不同表中减去两列。

我不知道这是否正确，但我想到的是SUM全部来自TblAddToInventory，使用GROUP BY，然后SUM全部来自TblWithdrawnFromInventory，使用GROUP BY，然后SUBTRACT列来自TblAddToInventory和TblWithdrawnFromInventory，使用GROUP BY。但我不认为这是个好主意。你能帮忙吗?

谢谢。

从具有相同ID的不同表中减去2列

我知道如何使用SUM和JOIN，但我只是不知道如何创建一个控件从不同的表中减去两列的语法相同的ID。

这是你怎么做的代码: 

 SELECT inventory.ProductId, 
            inventory.Quantity - ISNULL(withdrawal.Quantity,0) AS Quantity, 
            inventory.Amount - ISNULL(withdrawal.Amount,0) AS Amount
        FROM (
            SELECT ProductId, SUM(Quantity) AS Quantity, SUM(Amount) AS Amount
            FROM TblAddToInventory
            GROUP BY ProductId
        ) AS inventory
        LEFT JOIN (
            SELECT ProductId, SUM(Quantity) AS Quantity, SUM(Amount) AS Amount
            FROM TblWithdrawnFromInventory
            GROUP BY ProductId
        ) AS withdrawal ON inventory.ProductId = withdrawal.ProductId

准备:

——用数据创建临时表，强制转换第一行的日期以设置正确的数据类型 

select * into #tblAddToInventory from (
select 1 as ProductID, 2 as Quantity, 2.00 as Amount, cast('7/7/2012' as date) as [Date]
union all select 2 as ProductID, 3 as Quantity, 3.00 as Amount, '7/7/2012' as Date 
union all select 3 as ProductID, 4 as Quantity, 4.00 as Amount, '7/7/2012' as Date 
union all select 2 as ProductID, 2 as Quantity, 2.00 as Amount, '7/8/2012' as Date 
union all select 3 as ProductID, 3 as Quantity, 3.00 as Amount, '7/8/2012' as Date 
) a

——用数据创建临时表，强制转换第一行的日期以设置正确的数据类型

select * into #tblWithdrawnFromInventory from (
select 2 as ProductID, 4 as Quantity, 4.00 as Amount, cast('7/9/2012' as date) as [Date]
union all select 3 as ProductID, 5 as Quantity, 5.00 as Amount, '7/10/2012' as Date 
) b

——校验数据是否正确

select * from #tblAddToInventory
--    ProductID   Quantity    Amount      Date
--    ----------- ----------- ----------- ----------
--    1           2           2.00        2012-07-07
--    2           3           3.00        2012-07-07
--    3           4           4.00        2012-07-07
--    2           2           2.00        2012-07-08
--    3           3           3.00        2012-07-08

——校验数据是否正确

select * from #tblWithdrawnFromInventory
--    ProductID   Quantity    Amount      Date
--    ----------- ----------- ----------- ----------
--    2           4           4.00        2012-07-09
--    3           5           5.00        2012-07-10
<标题>开始的解决方案:

——使用Union All连接查询，并将第二个查询乘以-1使其为负

select * from #tblAddToInventory union all 
select ProductID, (Quantity * -1) as Quantity, Amount, Date from #tblWithdrawnFromInventory
--    ProductID   Quantity    Amount      Date
--    ----------- ----------- ----------- ----------
--    1           2           2.00        2012-07-07
--    2           3           3.00        2012-07-07
--    3           4           4.00        2012-07-07
--    2           2           2.00        2012-07-08
--    3           3           3.00        2012-07-08
--    2           -4          4.00        2012-07-09
--    3           -5          5.00        2012-07-10
select ProductID, sum(Quantity) as Quantity, sum(Amount) as Amount from (
    select * from #tblAddToInventory union all 
    select ProductID, (Quantity * -1) as Quantity, (Amount * -1) as Amount, Date from #tblWithdrawnFromInventory
) joinedData
where [Date] >= '7/6/2012' and [Date] <= '7/11/2012'
group by ProductID
--    ProductID   Quantity    Amount
--    ----------- ----------- -----------
--    1           2           2.00
--    2           1           1.00
--    3           2           2.00

——delete temp tables

drop table #tblAddToInventory
drop table #tblWithdrawnFromInventory
相关文章：