從表1中減去表2中的多個數量

如何從 table1 中減去 table2 中的數量？

我正在使用 SQL Server 2014。

表格1

P_ID     name       quantity
1       carrot        100
2       eggplant      100
3       Avocado       100
4       Cabbage       100
5       peppers       100

表2

  P_ID      name       quantity
   1       carrot        3
   2       eggplant      12
   5       peppers       7
   2       eggplant      8
   1       carrot        25
   5       peppers       13

最終結果應該是這樣的：

表格1

P_ID        name     quantity
1            a         73
2            b         80
3            c         100
4            d         100
5            e         80

我怎樣才能做到這一點？這甚至可能嗎？

這是一個如何做到這一點的例子

--Demo setup
Declare @Table1 table (P_ID int, [name] varchar(1), quantity int)
INSERT INTO @Table1
   (P_ID, [name], quantity)
VALUES
   (1, 'a', 100),
   (2, 'b', 100),
   (3, 'c', 100),
   (4, 'd', 100),
   (5, 'e', 100)
;

Declare @Table2 TABLE
   (P_ID int, [name] varchar(8), quantity int)
;

INSERT INTO @Table2
   (P_ID, [name], quantity)
VALUES
   (1, 'carrot', 3),
   (2, 'eggplant', 12),
   (5, 'peppers', 7)
;

--The solution
UPDATE t1
SET t1.quantity = t1.quantity - t2.quantity
FROM @Table1 t1
JOIN @Table2 t2
   ON t2.P_id = t1.P_ID

SELECT *
FROM @Table1

---

| P_ID | name | quantity |
|------|------|----------|
| 1    | a    | 97       |
| 2    | b    | 88       |
| 3    | c    | 100      |
| 4    | d    | 100      |
| 5    | e    | 93       |

---

根據OP關於分組表2的評論進行更新

Declare @Table1 table (P_ID int, [name] varchar(1), quantity int)
INSERT INTO @Table1
   (P_ID, [name], quantity)
VALUES
   (1, 'a', 100),
   (2, 'b', 100),
   (3, 'c', 100),
   (4, 'd', 100),
   (5, 'e', 100)
;
Declare @Table2 table
   ([P_id] int, [name] varchar(20), [quantity] int)
;

INSERT INTO @Table2
   ([P_ID], [name], [quantity])
VALUES
   (1, 'carrot', 3),
   (2, 'eggplant', 12),
   (5, 'peppers', 7),
   (2, 'eggplant', 8),
   (1, 'carrot', 25),
   (5, 'peppers', 13)
;



;WITH Table2Grouped
AS (
   SELECT P_ID
       ,SUM(cast(quantity AS INT)) AS quantity
   FROM @Table2
   GROUP BY P_ID
   )
UPDATE t1
SET t1.quantity = t1.quantity - t2.quantity
FROM @Table1 t1
JOIN Table2Grouped t2
   ON t2.P_id = t1.P_ID

SELECT *
FROM @Table1

引用自：https://dba.stackexchange.com/questions/219288