使用 OPENJSON，無法將結果傳遞給 GROUP BY

我將 JSON 字元串傳遞給儲存過程以插入倉庫庫存。

有時，同一貨盤 (LPN) 上的同一個貨架 (LPN) 上會有多個具有相同產品的行項目具有相同的日期。我想將這些匯總到表格中的一行。

JSON 表示已接收並存放到某個位置的各個行。這裡有2個相同的項目：

[{"StockCode": "ABC123", "Qty": "200", "Bin": "E4B4_L", "LPN": "1234", "PutDate": "2022-01-21 18:35:53 UTC"}, {"StockCode": "ABC123", "Qty": "400", "Bin": "E4B4_L", "LPN": "1234", "PutDate": "2022-01-21 18:36:43 UTC"}]

所以：

ABC | 200 |  2022-01-21 00:00:00.000 | LPN1234
ABC | 400 |  2022-01-21 00:00:00.000 | LPN1234

應匯總為：

ABC | 600 |  2022-01-21 00:00:00.000 | LPN1234

我嘗試在 qty 上進行 GROUP BY 和 SUM，但表中的結果行仍然是 2 個單獨的行。由於時間戳，我意識到 PutDate 不一樣，所以我認為肯定將其轉換為 DATE 會解決它，但事實並非如此。

SQL腳本：

ALTER Procedure spc_PutAway
(@json NVARCHAR(MAX) = '')
AS
BEGIN


INSERT INTO warehouse (Bin, StockCode, Qty, PutDate, VerDate, LPN)
SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDate AS DATE) as PutDate, GETDATE() as VerDate, LPN
   FROM OPENJSON(@json)
   WITH (
       Bin VARCHAR(20) '$.Bin',
       StockCode VARCHAR(30) '$.StockCode',
       Qty DECIMAL(18,6) '$.Qty',
       PutDate VARCHAR(20) '$.PutDate',
       LPN VARCHAR(50) '$.LPN'
       )
WHERE Bin <> 'DEFAULT'
GROUP BY StockCode, Bin, PutDate, LPN

END

表中的結果：

Bin StockCode   Qty PutDate VerDate LPN TransID VerCol
E4B4_L  ABC123  200.000000  2022-01-21 00:00:00.000 2022-01-21 10:52:43.823 1234    1   0x000000000000275D
E4B4_L  ABC123  400.000000  2022-01-21 00:00:00.000 2022-01-21 10:52:43.823 1234    2   0x000000000000275E

巧合的是，您的查詢甚至有效。

通常，您不能在GROUP BY查詢的子句中使用別名——您必須使用實際的列名。例如，如果您嘗試執行這個簡單的查詢，您將Invalid column name 'ObjectName'在嘗試執行查詢時收到錯誤（請注意，它會成功解析，但只會在執行時出錯）：

SELECT ObjectName = OBJECT_NAME(object_id),
       NumberColumns = COUNT(*)
FROM sys.columns
GROUP BY ObjectName;

要解決此問題，您可以使用非別名的列名，或者添加 CTE 或子查詢以強制抽象層並使其使查詢引擎能夠理解別名。

在您的查詢中，您巧合地為您的選擇中的粉碎列OPENJSON...WITH和計算的別名使用了相同的名稱。這掩蓋了真正的問題。如果您更改您使用的名稱以脫離 JSON 粉碎和 中的引用SELECT，但繼續在 中使用別名GROUP BY，您將收到一條Invalid column錯誤消息：

SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDateText AS DATE) as PutDate, GETDATE() as VerDate, LPN
   FROM OPENJSON(@json)
   WITH (
       Bin VARCHAR(20) '$.Bin',
       StockCode VARCHAR(30) '$.StockCode',
       Qty DECIMAL(18,6) '$.Qty',
       PutDateText VARCHAR(20) '$.PutDate', --changed this (and reference above)
       LPN VARCHAR(50) '$.LPN'
       )
WHERE Bin <> 'DEFAULT'
GROUP BY StockCode, Bin, PutDate, LPN;

順便說一句，現在我們已經更改了一些標籤，我們可以看到原始查詢的功能等效實際上是這樣的：

SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDateText AS DATE) as PutDate, GETDATE() as VerDate, LPN
   FROM OPENJSON(@json)
   WITH (
       Bin VARCHAR(20) '$.Bin',
       StockCode VARCHAR(30) '$.StockCode',
       Qty DECIMAL(18,6) '$.Qty',
       PutDateText VARCHAR(20) '$.PutDate',
       LPN VARCHAR(50) '$.LPN'
       )
WHERE Bin <> 'DEFAULT'
GROUP BY StockCode, Bin, PutDateText, LPN;

使這項工作的“快速”方法不是使用別名，而是使用直接列名（在這種情況下，使用CAST()函式的公式：

SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDateText AS DATE) as PutDate, GETDATE() as VerDate, LPN
   FROM OPENJSON(@json)
   WITH (
       Bin VARCHAR(20) '$.Bin',
       StockCode VARCHAR(30) '$.StockCode',
       Qty DECIMAL(18,6) '$.Qty',
       PutDateText VARCHAR(20) '$.PutDate',
       LPN VARCHAR(50) '$.LPN'
       )
WHERE Bin <> 'DEFAULT'
GROUP BY StockCode, Bin, CAST(PutDateText AS DATE), LPN

就個人而言，我會使用 CTE 將 JSON 分解為表格格式，然後使用 CTE 建構我的查詢來進行聚合。這對我來說感覺有點“乾淨”，並允許我在任何我想要的地方使用別名。這樣做的好處是不必重複函式呼叫，並使它們在 & 之間保持完美SELECT一致GROUP BY：

WITH JsonTable AS (
   SELECT Bin, StockCode, Qty, CAST(PutDate AS DATE) as PutDate, LPN
       FROM OPENJSON(@json)
       WITH (
           Bin VARCHAR(20) '$.Bin',
           StockCode VARCHAR(30) '$.StockCode',
           Qty DECIMAL(18,6) '$.Qty',
           PutDate VARCHAR(20) '$.PutDate',
           LPN VARCHAR(50) '$.LPN'
           )
)
INSERT INTO warehouse (Bin, StockCode, Qty, PutDate, VerDate, LPN)
SELECT Bin, 
       StockCode, 
       SUM(Qty) as Qty, 
       PutDate, 
       GETDATE() AS VerDate, 
       LPN
FROM JsonTable
WHERE Bin <> 'DEFAULT'
GROUP BY StockCode, Bin, PutDate, LPN;

但是您也可以以幾乎相同的方式使用內聯子查詢。

INSERT INTO warehouse (Bin, StockCode, Qty, PutDate, VerDate, LPN)
SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDate AS DATE) as PutDate, GETDATE() as VerDate, LPN
FROM (
       SELECT Bin, StockCode, Qty, CAST(PutDate AS DATE) as PutDate, LPN
           FROM OPENJSON(@json)
           WITH (
               Bin VARCHAR(20) '$.Bin',
               StockCode VARCHAR(30) '$.StockCode',
               Qty DECIMAL(18,6) '$.Qty',
               PutDate VARCHAR(20) '$.PutDate',
               LPN VARCHAR(50) '$.LPN'
               )
   ) AS x
WHERE Bin <> 'DEFAULT'
GROUP BY StockCode, Bin, PutDate, LPN;

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