使用OPENJSON解析表内JSON结合ORDER BY CASE时DISTINCT整表报错如何解决

你遇到的报错核心是SQL语法限制：当SELECT DISTINCT与ORDER BY联用时，ORDER BY子句中所有用到的列必须存在于SELECT的返回结果集中。你的语句中ORDER BY用到了OUTER APPLY打开JSON后返回的X表数据，但你SELECT只返回了items.*，这部分JSON相关的排序字段不在返回结果里，数据库无法建立去重结果和排序逻辑的对应关系，因此触发报错。

此外还有一个隐藏逻辑问题：如果单个Item对应的$.itemOrderPerGroup数组包含多个元素，OUTER APPLY会把同一个Item拆成多行，直接加DISTINCT也可能出现去重不完全、排序逻辑混乱的情况。

推荐用CTE先完成每个Item的排序权重计算，再执行去重和排序，避免语法冲突，示例代码如下：

DECLARE @FilteredItemIDs TABLE (ItemID INT) -- 替换为你实际的临时表定义
DECLARE @CurrentGroupID AS INT

WITH ItemSortCalc AS (
    SELECT 
        items.*,
        -- 计算排序优先级：匹配到当前分组的排最前
        SortPrio = MIN(CASE
            WHEN @CurrentGroupID != 0 AND JSON_VALUE(X.[Value], '$.Key') = @CurrentGroupID THEN 1
            ELSE 2
        END),
        -- 提取对应分组的排序值，未匹配的默认放最后
        OrderVal = MIN(CASE
            WHEN @CurrentGroupID != 0 AND JSON_VALUE(X.[Value], '$.Key') = @CurrentGroupID THEN TRY_CONVERT(INT, JSON_VALUE(X.[Value], '$.Value'))
            ELSE 2147483647
        END)
    FROM Items AS items
    OUTER APPLY OPENJSON(JSON_QUERY(Data, '$.itemOrderPerGroup'), '$') AS X
    WHERE items.ItemID IN (SELECT ItemID FROM @FilteredItemIDs)
    -- ItemID为主键时仅需GROUP BY ItemID即可，低版本SQL Server需要把items所有返回列都加入GROUP BY
    GROUP BY items.ItemID, items.Data
)
SELECT DISTINCT *
FROM ItemSortCalc
ORDER BY SortPrio, OrderVal

如果你用的是SQL Server 2017及以上版本，也可以用窗口函数方案实现更高效的去重排序：

DECLARE @FilteredItemIDs TABLE (ItemID INT)
DECLARE @CurrentGroupID AS INT

WITH ItemRanked AS (
    SELECT 
        items.*,
        ROW_NUMBER() OVER(
            PARTITION BY items.ItemID
            ORDER BY 
                CASE WHEN @CurrentGroupID != 0 AND JSON_VALUE(X.[Value], '$.Key') = @CurrentGroupID THEN 1 ELSE 2 END,
                TRY_CONVERT(INT, JSON_VALUE(X.[Value], '$.Value'))
        ) AS rn,
        SortPrio = CASE WHEN @CurrentGroupID != 0 AND JSON_VALUE(X.[Value], '$.Key') = @CurrentGroupID THEN 1 ELSE 2 END,
        OrderVal = TRY_CONVERT(INT, JSON_VALUE(X.[Value], '$.Value'))
    FROM Items AS items
    OUTER APPLY OPENJSON(JSON_QUERY(Data, '$.itemOrderPerGroup'), '$') AS X
    WHERE items.ItemID IN (SELECT ItemID FROM @FilteredItemIDs)
)
-- 低版本SQL Server不支持EXCEPT语法，手动列出需要返回的列即可
SELECT * EXCEPT(rn, SortPrio, OrderVal)
FROM ItemRanked
WHERE rn = 1
ORDER BY SortPrio, OrderVal

• 建议用TRY_CONVERT代替CONVERT做类型转换，避免JSON中存在非数字值时触发转换报错
• 如果ItemID是Items表的主键，使用ROW_NUMBER方案效率更高，不需要额外执行DISTINCT
• 如果你的JSON数组中同一个Key可能出现多次，根据实际业务需求把聚合函数MIN替换为MAX即可

内容的提问来源于stack exchange，提问作者TProgram