按指定顺序更新LocationContainers表sequence字段的SQL查询需求

我在使用UPDATE语句时遇到问题，当前查询语句如下：

select      
    lc.name as locations,
    lc.sequence
from        
    dbo.LocationContainers lc

当前该查询返回结果：

locations       sequence
-------------------------
014-001-010-01  10
014-001-010-02  10
014-001-010-03  10
014-001-010-04  10
014-001-010-05  10
014-001-010-06  10
014-001-010-07  10
014-001-010-08  10
014-001-010-09  10
014-001-010-10  10
014-001-020-01  10
014-001-020-02  10
014-001-020-03  10
014-001-020-04  10
014-001-020-05  10
014-001-020-06  10
014-001-020-07  10
014-001-020-08  10
014-001-020-09  10
014-001-020-10  10
014-001-030-01  10
014-001-030-02  10
014-001-030-03  10
014-001-030-04  10
014-001-030-05  10
014-001-030-06  10
014-001-030-07  10
014-001-030-08  10
014-001-030-09  10
014-001-030-10  10
014-030-010-01  10
014-030-010-02  10
014-030-010-03  10
014-030-010-04  10
014-030-010-05  10
014-030-010-06  10
014-030-010-07  10
014-030-010-08  10
014-030-010-09  10
014-030-010-10  10
014-030-020-01  10
014-030-020-02  10
014-030-020-03  10
014-030-020-04  10
014-030-020-05  10
014-030-020-06  10
014-030-020-07  10
014-030-020-08  10
014-030-020-09  10
014-030-020-10  10
014-030-030-01  10
014-030-030-02  10
014-030-030-03  10
014-030-030-04  10
014-030-030-05  10
014-030-030-06  10
014-030-030-07  10
014-030-030-08  10
014-030-030-09  10
014-030-030-10  10

需要按指定顺序更新sequence字段：从location为014-001-010-01的sequence设为10开始，到location为014-030-030-10的sequence设为610结束。具体规则为：按步长10依次赋值，顺序为014-001-010-01=10，接着014-030-010-01=20，然后014-001-010-02=30，014-030-010-02=40，以此类推，最终目标结果如下：

locations       sequence
------------------------
014-001-010-01  10
014-001-010-02  30
014-001-010-03  50
014-001-010-04  70
014-001-010-05  90
014-001-010-06  110
014-001-010-07  130
014-001-010-08  150
014-001-010-09  170
014-001-010-10  190
014-001-020-01  210
014-001-020-02  230
014-001-020-03  250
014-001-020-04  270
014-001-020-05  290
014-001-020-06  310
014-001-020-07  330
014-001-020-08  350
014-001-020-09  370
014-001-020-10  390
014-001-030-01  410
014-001-030-02  430
014-001-030-03  450
014-001-030-04  470
014-001-030-05  490
014-001-030-06  510
014-001-030-07  530
014-001-030-08  550
014-001-030-09  570
014-001-030-10  600
014-030-010-01  20
014-030-010-02  40
014-030-010-03  60
014-030-010-04  80
014-030-010-05  100
014-030-010-06  120
014-030-010-07  140
014-030-010-08  160
014-030-010-09  180
014-030-010-10  200
014-030-020-01  220
014-030-020-02  240
014-030-020-03  260
014-030-020-04  280
014-030-020-05  300
014-030-020-06  320
014-030-020-07  340
014-030-020-08  360
014-030-020-09  380
014-030-020-10  400
014-030-030-01  420
014-030-030-02  440
014-030-030-03  460
014-030-030-04  480
014-030-030-05  500
014-030-030-06  520
014-030-030-07  540
014-030-030-08  560
014-030-030-09  580
014-030-030-10  610

解决方案 #

可以利用CTE结合窗口函数生成符合要求的排序行号，再通过行号计算对应的sequence值：

WITH RankedLocations AS (
    SELECT 
        name,
        sequence,
        -- 拆分location的四段部分，用于排序逻辑
        PARSENAME(REPLACE(name, '-', '.'), 4) AS Part1,
        PARSENAME(REPLACE(name, '-', '.'), 3) AS Part2,
        PARSENAME(REPLACE(name, '-', '.'), 2) AS Part3,
        PARSENAME(REPLACE(name, '-', '.'), 1) AS Part4,
        -- 按第三段（010/020/030）、第四段（01-10）排序，同组内先取001开头的再取030开头的
        ROW_NUMBER() OVER (ORDER BY Part3, Part4, CASE Part2 WHEN '001' THEN 1 ELSE 2 END) AS RowNum
    FROM dbo.LocationContainers
)
UPDATE RankedLocations
SET sequence = RowNum * 10;

逻辑说明 #

1. 使用PARSENAME和REPLACE拆分location字段为四段（将-替换为.后，PARSENAME从右往左取部分），方便分组排序。
2. 通过ROW_NUMBER()生成行号：先按第三段（如010、020）排序，再按第四段（如01、02）排序，同第三段和第四段的情况下，001开头的行排在030开头的前面。
3. 最终sequence值为行号乘以10，正好符合步长10的赋值要求。

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