如何使用CAST函数对SQL分组查询结果进行数值排序？

方案1：直接在ORDER BY中提取数字转换为整数 #

你可以在ORDER BY里提取Rango字段中冒号前的数字部分，通过CAST转成整数后排序，这样就能避免字符串排序的问题：

SELECT
    CASE 
        WHEN TotalDue >= 0 AND TotalDue <= 99 THEN '1: 0-99' 
        WHEN TotalDue >= 100 AND TotalDue <= 499 THEN '2: 100-499'
        WHEN TotalDue >= 500 AND TotalDue <= 1000 THEN '3: 500-1000'
        WHEN TotalDue > 1000 AND TotalDue <= 9999 THEN '4: 1000-9999'
        WHEN TotalDue > 10000 THEN '5: 10000 en adelante'
    END AS Rango,
    COUNT(SalesOrderID) AS 'Cantidad de Órdenes', 
    SUM(TotalDue) AS 'Valor Total'
FROM SalesLT.SalesOrderHeader 
GROUP BY 
    CASE 
        WHEN TotalDue >= 0 AND TotalDue <= 99 THEN '1: 0-99' 
        WHEN TotalDue >= 100 AND TotalDue <= 499 THEN '2: 100-499'
        WHEN TotalDue >= 500 AND TotalDue <= 1000 THEN '3: 500-1000'
        WHEN TotalDue > 1000 AND TotalDue <= 9999 THEN '4: 1000-9999'
        WHEN TotalDue > 10000 THEN '5: 10000 en adelante'
    END 
ORDER BY CAST(SUBSTRING(Rango, 1, CHARINDEX(':', Rango) - 1) AS INT)

方案2：新增排序用的数值字段（更高效） #

如果数据量较大，推荐用子查询生成一个专门的排序数值字段，这样排序时不需要重复处理字符串，性能更优：

SELECT
    Rango,
    COUNT(SalesOrderID) AS 'Cantidad de Órdenes', 
    SUM(TotalDue) AS 'Valor Total'
FROM (
    SELECT
        SalesOrderID,
        TotalDue,
        CASE 
            WHEN TotalDue >= 0 AND TotalDue <= 99 THEN '1: 0-99' 
            WHEN TotalDue >= 100 AND TotalDue <= 499 THEN '2: 100-499'
            WHEN TotalDue >= 500 AND TotalDue <= 1000 THEN '3: 500-1000'
            WHEN TotalDue > 1000 AND TotalDue <= 9999 THEN '4: 1000-9999'
            WHEN TotalDue > 10000 THEN '5: 10000 en adelante'
        END AS Rango,
        CASE 
            WHEN TotalDue >= 0 AND TotalDue <= 99 THEN 1 
            WHEN TotalDue >= 100 AND TotalDue <= 499 THEN 2
            WHEN TotalDue >= 500 AND TotalDue <= 1000 THEN 3
            WHEN TotalDue > 1000 AND TotalDue <= 9999 THEN 4
            WHEN TotalDue > 10000 THEN 5
        END AS SortKey
    FROM SalesLT.SalesOrderHeader
) AS OrderGroups
GROUP BY Rango, SortKey
ORDER BY SortKey

两种方案都能实现正确的数值排序，方案2因为直接基于整数排序，执行效率更高。

内容的提问来源于stack exchange，提问作者Sebastián Mata