為行組添加總量列

我有一個 Items 表和一個 Inventory 表。一個項目可以屬於多個庫存記錄。我正在嘗試返回所有庫存記錄的列表，但它們的數量在新列中。例如：

項目

ItemID     ItemDescription
103        Headphones
115        Speakers
230        Wireless Adapter
275        20' Network Cable

存貨

InventoryID        ItemID        WarrantyDate       Status
1                  103           12/22/2010         Available
2                  103           05/15/2012         Available
3                  103           02/24/2015
4                  275           01/01/2010
5                  275           01/01/2011

如果我嘗試COUNT使用 ItemID 和GROUP BYItemID，如下所示：

SELECT ItemID, COUNT(ItemID) AS Quantity
FROM Inventory
GROUP BY ItemID

我得到：

ItemID  Quantity
103     3
275     2        

但我真正想要的是：

InventoryID        ItemID        WarrantyDate       Status       Quantity
1                  103           12/22/2010         Available    3
2                  103           05/15/2012         Available    3
3                  103           02/24/2015                      3
4                  275           01/01/2010                      2
5                  275           01/01/2011                      2

任何建議/想法表示讚賞。

您可以使用函式OVER上的子句COUNT來獲得所需的內容：

CREATE TABLE #inventory(
  InventoryID  INTEGER  NOT NULL PRIMARY KEY 
 ,ItemID       INTEGER  NOT NULL
 ,WarrantyDate DATE  NOT NULL
 ,Status       VARCHAR(9)
);
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (1,103,'12/22/2010','Available');
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (2,103,'05/15/2012','Available');
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (3,103,'02/24/2015',NULL);
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (4,275,'01/01/2010',NULL);
INSERT INTO #inventory(InventoryID,ItemID,WarrantyDate,Status) VALUES (5,275,'01/01/2011',NULL);

SELECT *, COUNT(ItemID) OVER (PARTITION BY ItemID) AS Quantity
FROM #Inventory

輸出：

+-------------+--------+--------------+-----------+----------+
| InventoryID | ItemID | WarrantyDate |  Status   | Quantity |
+-------------+--------+--------------+-----------+----------+
|           1 |    103 | 2010-12-22   | Available |        3 |
|           2 |    103 | 2012-05-15   | Available |        3 |
|           3 |    103 | 2015-02-24   | NULL      |        3 |
|           4 |    275 | 2010-01-01   | NULL      |        2 |
|           5 |    275 | 2011-01-01   | NULL      |        2 |
+-------------+--------+--------------+-----------+----------+

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