主页 / dba / 问题 / 306522
Accepted
hap76
Asked: 2022-01-25 10:35:26 +0800 CST

使用 OPENJSON，无法将结果传递给 GROUP BY

  • 772

我将 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
sql-server insert

1 个回答

  1. Best Answer

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

    通常，您不能在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;
    • 5

相关问题