基于Level奇偶值排序Bay的SQL脚本开发及错误修复求助

需要编写SQL脚本，按Level值对Bay字段排序：Level为奇数时Bay升序，Level为偶数时Bay降序。共有12个Level，每个Level对应54个Bay。

示例数据 #

原始数据：

Level  Bay
    1    5
    1    4
    1   54
    1   13
    2    8
    2    3
    2    4

期望结果：

Level  Bay
    1    4
    1    5
    1   13
    1   54
    2    8
    2    4
    2    3

现有脚本问题 #

当前脚本执行时出现varchar转int失败的错误，脚本如下：

SELECT cast (SUBSTRING(cm.description, CHARINDEX('Bay:', cm.description) + 4, CHARINDEX(' ', cm.description, CHARINDEX('Bay:', cm.description)) - CHARINDEX('Bay:', cm.description) - 4) AS INT)      AS Bay,
       cast (SUBSTRING(cm.description, CHARINDEX('Level:', cm.description) + 6, CHARINDEX(' ', cm.description, CHARINDEX('Level:', cm.description)) - CHARINDEX('Level:', cm.description) - 6)AS INT) AS level
FROM   [Test].[Test].[Stations] AS cm
WHERE  ( cm.StationTypeId = '1' )
ORDER  BY CASE 'level' % 2
            WHEN 0 THEN 'bay'
            WHEN 1 THEN '-bay'
          END; 

数据的description字段格式示例：

Aisle:1 Bay:1 Level:1 Side:E

解决方案 #

问题分析 #

1. ORDER BY子句中使用了字符串字面量'level'和'bay'，而非字段别名，导致排序逻辑完全错误。
2. 提取Level和Bay时，若description末尾无空格，CHARINDEX会返回0，截取的字符串无效，触发类型转换失败。

修正后的脚本 #

通过拼接空格避免末尾无分隔符的问题，同时正确引用字段别名实现条件排序：

SELECT 
    -- 提取Bay并转换为int，拼接空格确保能找到分隔符
    CAST(SUBSTRING(cm.description, CHARINDEX('Bay:', cm.description) + 4, 
                   CHARINDEX(' ', cm.description + ' ', CHARINDEX('Bay:', cm.description)) - CHARINDEX('Bay:', cm.description) - 4) AS INT) AS Bay,
    -- 提取Level并转换为int
    CAST(SUBSTRING(cm.description, CHARINDEX('Level:', cm.description) + 6, 
                   CHARINDEX(' ', cm.description + ' ', CHARINDEX('Level:', cm.description)) - CHARINDEX('Level:', cm.description) - 6) AS INT) AS Level
FROM [Test].[Test].[Stations] AS cm
WHERE cm.StationTypeId = '1'
-- 先按Level分组，再根据奇偶性对Bay排序
ORDER BY 
    Level,
    CASE 
        WHEN Level % 2 = 1 THEN Bay  -- 奇数Level，Bay升序
        ELSE -Bay                    -- 偶数Level，取反后升序等价于原Bay降序
    END;

补充说明 #

如果Bay是字符串类型（非数值），可拆分排序条件实现：

ORDER BY 
    Level,
    CASE WHEN Level % 2 = 1 THEN Bay END ASC,
    CASE WHEN Level % 2 = 0 THEN Bay END DESC;

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