Sql 使用 union 的有序结果获取行数

我有一个包含6个联合的查询，用于获取6个不同级别的数据，然后对其进行排序.

更新的数据库字段:https://dbfiddle.uk/zNH3Xjog

以下是基本数据:

我需要使用该查询来使用排序后的订单更新数据库中的另一个字段，因此我想返回一个计数器，并使用该计数器和ID字段进行更新.

以下是查询:

SELECT
    LEVEL1_DATA.PICKSORTVALUE AS LEVEL1SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL2SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL3SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL4SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL5SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
    LEVEL1_DATA.SHELFLOCID
FROM
    INVENTORYSHELFLOCATIONS LEVEL1_DATA
WHERE
    LEVEL1_DATA.DEPTH = 1
    AND LEVEL1_DATA.LOCATIONID = 24541891
  
UNION

SELECT
    LEVEL2_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
    LEVEL2_DATA.PICKSORTVALUE AS LEVEL2SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL3SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL4SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL5SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
    LEVEL2_DATA.SHELFLOCID
FROM
    INVENTORYSHELFLOCATIONS LEVEL2_DATA
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL2_LEVEL1 ON LEVEL2_LEVEL1.SHELFLOCID = LEVEL2_DATA.PARENTSHELFLOCID
WHERE
    LEVEL2_DATA.DEPTH = 2
    AND LEVEL2_DATA.LOCATIONID = 24541891

UNION

SELECT
    LEVEL3_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
    LEVEL3_LEVEL2.PICKSORTVALUE AS LEVEL2SORT,
    LEVEL3_DATA.PICKSORTVALUE AS LEVEL3SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL4SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL5SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
    LEVEL3_DATA.SHELFLOCID
FROM
    INVENTORYSHELFLOCATIONS LEVEL3_DATA
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL3_LEVEL2 ON LEVEL3_LEVEL2.SHELFLOCID = LEVEL3_DATA.PARENTSHELFLOCID
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL3_LEVEL1 ON LEVEL3_LEVEL1.SHELFLOCID = LEVEL3_LEVEL2.PARENTSHELFLOCID
WHERE
    LEVEL3_DATA.DEPTH = 3
    AND LEVEL3_DATA.LOCATIONID = 24541891

UNION

SELECT
    LEVEL4_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
    LEVEL4_LEVEL2.PICKSORTVALUE AS LEVEL2SORT,
    LEVEL4_LEVEL3.PICKSORTVALUE AS LEVEL3SORT,
    LEVEL4_DATA.PICKSORTVALUE AS LEVEL4SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL5SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
    LEVEL4_DATA.SHELFLOCID
FROM
    INVENTORYSHELFLOCATIONS LEVEL4_DATA
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL4_LEVEL3 ON LEVEL4_LEVEL3.SHELFLOCID = LEVEL4_DATA.PARENTSHELFLOCID
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL4_LEVEL2 ON LEVEL4_LEVEL2.SHELFLOCID = LEVEL4_LEVEL3.PARENTSHELFLOCID
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL4_LEVEL1 ON LEVEL4_LEVEL1.SHELFLOCID = LEVEL4_LEVEL2.PARENTSHELFLOCID
WHERE
    LEVEL4_DATA.DEPTH = 4
    AND LEVEL4_DATA.LOCATIONID = 24541891

UNION

SELECT
    LEVEL5_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
    LEVEL5_LEVEL2.PICKSORTVALUE AS LEVEL2SORT,
    LEVEL5_LEVEL3.PICKSORTVALUE AS LEVEL3SORT,
    LEVEL5_LEVEL4.PICKSORTVALUE AS LEVEL4SORT,
    LEVEL5_DATA.PICKSORTVALUE AS LEVEL5SORT,
    CAST(0 AS NUMERIC(10,0)) AS LEVEL6SORT,
    LEVEL5_DATA.SHELFLOCID
FROM
    INVENTORYSHELFLOCATIONS LEVEL5_DATA
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL5_LEVEL4 ON LEVEL5_LEVEL4.SHELFLOCID = LEVEL5_DATA.PARENTSHELFLOCID
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL5_LEVEL3 ON LEVEL5_LEVEL3.SHELFLOCID = LEVEL5_LEVEL4.PARENTSHELFLOCID
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL5_LEVEL2 ON LEVEL5_LEVEL2.SHELFLOCID = LEVEL5_LEVEL3.PARENTSHELFLOCID
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL5_LEVEL1 ON LEVEL5_LEVEL1.SHELFLOCID = LEVEL5_LEVEL2.PARENTSHELFLOCID
WHERE
    LEVEL5_DATA.DEPTH = 5
    AND LEVEL5_DATA.LOCATIONID = 24541891

UNION

SELECT
    LEVEL6_LEVEL1.PICKSORTVALUE AS LEVEL1SORT,
    LEVEL6_LEVEL2.PICKSORTVALUE AS LEVEL2SORT,
    LEVEL6_LEVEL3.PICKSORTVALUE AS LEVEL3SORT,
    LEVEL6_LEVEL4.PICKSORTVALUE AS LEVEL4SORT,
    LEVEL6_LEVEL5.PICKSORTVALUE AS LEVEL5SORT,
    LEVEL6_DATA.PICKSORTVALUE AS LEVEL6SORT,
    LEVEL6_DATA.SHELFLOCID
FROM
    INVENTORYSHELFLOCATIONS LEVEL6_DATA
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL6_LEVEL5 ON LEVEL6_LEVEL5.SHELFLOCID = LEVEL6_DATA.PARENTSHELFLOCID
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL6_LEVEL4 ON LEVEL6_LEVEL4.SHELFLOCID = LEVEL6_LEVEL5.PARENTSHELFLOCID
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL6_LEVEL3 ON LEVEL6_LEVEL3.SHELFLOCID = LEVEL6_LEVEL4.PARENTSHELFLOCID
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL6_LEVEL2 ON LEVEL6_LEVEL2.SHELFLOCID = LEVEL6_LEVEL3.PARENTSHELFLOCID
LEFT JOIN
    INVENTORYSHELFLOCATIONS LEVEL6_LEVEL1 ON LEVEL6_LEVEL1.SHELFLOCID = LEVEL6_LEVEL2.PARENTSHELFLOCID
WHERE
    LEVEL6_DATA.DEPTH = 6
    AND LEVEL6_DATA.LOCATIONID = 24541891
ORDER BY
    1, 2, 3, 4, 5, 6

这将返回一个对前6列进行排序的结果集，这些列是在程序本身的保存操作中预先排序的值(使用PICKSORTVALUE和DISPLAYORDER).

结果如下所示:

然后，我希望在更新查询中使用行计数器和SHELFLOCID字段:

UPDATE INVENTORYSHELFLOCATIONS 
SET PICKORDER = 1, 2, 3, 4,etc
WHERE SHELFLOCID = 55, 33, 88, 11,etc

我可以逐个遍历结果并更新记录，这需要大约4秒的时间来处理，但我希望在数据库级别做一些事情.

它必须在单个查询中完成，并且不能在查询中包含多个部分.

更新后的数据库将如下所示:

这是可以做到的吗？