如何用单条SQL为UNION排序结果加行号并批量更新PICKORDER

逐条更新的性能瓶颈在于多次网络请求、事务提交开销以及单条更新的锁资源竞争，改用数据库原生的批量更新能一次性完成所有操作，耗时会大幅降低（通常从秒级降到毫秒级，取决于数据量）。

实现思路 #

1. 将你现有的6个UNION查询整合为一个数据集，通过窗口函数ROW_NUMBER()按照指定排序规则生成行号，作为PICKORDER的新值。
2. 用UPDATE语句关联这个排序后的数据集，批量更新对应的SHELFLOCID记录。

具体SQL示例（以SQL Server为例） #

WITH SortedLocations AS (
    -- 替换为你原本的6个UNION查询，确保返回SHELFLOCID和各层级排序字段
    SELECT SHELFLOCID, LEVEL1SORT, LEVEL2SORT, LEVEL3SORT, LEVEL4SORT, LEVEL5SORT, LEVEL6SORT
    FROM INVENTORYSHELFLOCATIONS
    WHERE LOCATIONID = 24541891 AND DEPTH = 1
    UNION ALL  -- 若无重复数据，用UNION ALL替代UNION，避免不必要的去重开销
    SELECT SHELFLOCID, LEVEL1SORT, LEVEL2SORT, LEVEL3SORT, LEVEL4SORT, LEVEL5SORT, LEVEL6SORT
    FROM INVENTORYSHELFLOCATIONS
    WHERE LOCATIONID = 24541891 AND DEPTH = 2
    -- 继续补充DEPTH=3到6的查询
),
OrderedRows AS (
    SELECT 
        SHELFLOCID,
        ROW_NUMBER() OVER (ORDER BY LEVEL1SORT, LEVEL2SORT, LEVEL3SORT, LEVEL4SORT, LEVEL5SORT, LEVEL6SORT) AS NEW_PICKORDER
    FROM SortedLocations
)
UPDATE INVENTORYSHELFLOCATIONS
SET PICKORDER = ors.NEW_PICKORDER
FROM INVENTORYSHELFLOCATIONS isl
JOIN OrderedRows ors ON isl.SHELFLOCID = ors.SHELFLOCID;

其他数据库适配说明 #

• MySQL：需要调整UPDATE关联语法

UPDATE INVENTORYSHELFLOCATIONS isl
JOIN (
    SELECT 
        SHELFLOCID,
        ROW_NUMBER() OVER (ORDER BY LEVEL1SORT, LEVEL2SORT, LEVEL3SORT, LEVEL4SORT, LEVEL5SORT, LEVEL6SORT) AS NEW_PICKORDER
    FROM (
        -- 你的6个UNION ALL查询，或合并为DEPTH BETWEEN 1 AND 6（如果逻辑允许）
        SELECT SHELFLOCID, LEVEL1SORT, LEVEL2SORT, LEVEL3SORT, LEVEL4SORT, LEVEL5SORT, LEVEL6SORT
        FROM INVENTORYSHELFLOCATIONS
        WHERE LOCATIONID = 24541891 AND DEPTH BETWEEN 1 AND 6
    ) AS SortedLocations
) AS OrderedRows ON isl.SHELFLOCID = OrderedRows.SHELFLOCID
SET isl.PICKORDER = OrderedRows.NEW_PICKORDER;

• Oracle：可以使用MERGE语句

MERGE INTO INVENTORYSHELFLOCATIONS isl
USING (
    SELECT 
        SHELFLOCID,
        ROW_NUMBER() OVER (ORDER BY LEVEL1SORT, LEVEL2SORT, LEVEL3SORT, LEVEL4SORT, LEVEL5SORT, LEVEL6SORT) AS NEW_PICKORDER
    FROM (
        -- 你的6个UNION ALL查询
        SELECT SHELFLOCID, LEVEL1SORT, LEVEL2SORT, LEVEL3SORT, LEVEL4SORT, LEVEL5SORT, LEVEL6SORT
        FROM INVENTORYSHELFLOCATIONS
        WHERE LOCATIONID = 24541891 AND DEPTH = 1
        UNION ALL
        SELECT SHELFLOCID, LEVEL1SORT, LEVEL2SORT, LEVEL3SORT, LEVEL4SORT, LEVEL5SORT, LEVEL6SORT
        FROM INVENTORYSHELFLOCATIONS
        WHERE LOCATIONID = 24541891 AND DEPTH = 2
        -- 补充DEPTH3-6的查询
    ) AS SortedLocations
) src
ON (isl.SHELFLOCID = src.SHELFLOCID)
WHEN MATCHED THEN UPDATE SET isl.PICKORDER = src.NEW_PICKORDER;

性能优化建议 #

• 确认SHELFLOCID字段有唯一索引或主键，确保JOIN操作的效率。
• 如果你的6个UNION查询没有重复数据，务必用UNION ALL替代UNION，减少数据库去重的计算开销。
• 若数据量较大，可以先测试排序后的数据集是否正确，再执行更新操作。

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