在TSQL中按类型计算含数字/字母数字列的整数排序索引

解决方案代码 #

SELECT 
    type,
    code,
    ROW_NUMBER() OVER (
        PARTITION BY type 
        ORDER BY 
            -- 可转整数的code优先排在前面
            CASE WHEN TRY_CAST(code AS INT) IS NOT NULL THEN 0 ELSE 1 END,
            -- 可转整数的code按其整数值排序
            TRY_CAST(code AS INT),
            -- 非整数型code按字符串本身排序
            code
    ) AS sort_index
FROM your_table_name;

关键逻辑说明 #

• 分组规则：通过PARTITION BY type实现按type字段分组，每组的sort_index从1开始重新计数。
• 优先级区分：用TRY_CAST(code AS INT)判断code是否能转换为整数，能转换的标记为0，优先排序；不能转换的标记为1，排在后面。
• 内部排序：
  • 可转换为整数的code，按其实际整数值排序（例如050转为50，1转为1，因此1会排在050前面）。
  • 无法转换为整数的code，直接按字符串的自然顺序排序（例如Apple 1 < Apple 2 < Banana）。

结果验证 #

执行上述代码后，生成的sort_index完全符合预期：

• Fruit组：可转整数的030、040先排序，接着是字符串类型的Apple 1、Apple 2、Banana，序号从1到5。
• Soda组：可转整数的1、050、054按整数值排序，接着是Fanta、Sprite，序号从1到5。

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