SQL中的Alpha数字字符串订购

Question

我正在处理一个看起来像这样的查询：

SELECT
  CASE 
    WHEN num BETWEEN 1 AND 5 THEN '1-5'
    WHEN num BETWEEN 6 AND 10 THEN '6-10'
    WHEN num BETWEEN 11 AND 20 THEN '11-20'
    WHEN num > 20 THEN '20+'
END AS bucket,
COUNT(1)
FROM data
GROUP BY 1

...

我想按不同的桶值订购结果，因此结果看起来像该

水桶	计数（1）
1-5	10
6-10	11
11-20	17
20+	8

由于存储桶是一个字符串，因此只需按该列排序就无法解决问题。

SQL中是否有任何字符串操作可以帮助我？出于原因，我不会进入案例陈述是最简单的方法，使我以所需的格式获得结果，因此我宁愿处理一堆字符串操作，而不是按照我想要的顺序进行操作查询

Answer 1

我的方法使用桶的整数下限（因为整数比字符串更好），并且仅在末尾生成字符串存储桶名称， 分组后。

WITH histogramData AS (
    SELECT
        CASE
            WHEN num <  1 THEN NULL
            WHEN num <  6 THEN 1
            WHEN num < 11 THEN 6
            WHEN num < 21 THEN 11 ELSE 21
        END AS Bucket,
        data.*
    FROM
        data
),
grouped AS (
    SELECT
        h.Bucket,
        COUNT(*) AS [Count]
    FROM
        histogramData AS h
    GROUP BY
        h.Bucket
)
SELECT
    CASE ISNULL( g.Bucket, 0 )
        WHEN  0 THEN NULL,
        WHEN  1 THEN '1-5'
        WHEN  6 THEN '6-10'
        WHEN 11 THEN '11-20' ELSE '21+'
    END AS [Bucket Name],
    g.[Count]
FROM
    grouped
ORDER BY
    g.Bucket 

Answer 2

SELECT
  CASE 
    WHEN num BETWEEN 1 AND 5 THEN '01-05'
    WHEN num BETWEEN 6 AND 10 THEN '06-10'
    WHEN num BETWEEN 11 AND 20 THEN '11-20'
    WHEN num > 20 THEN '20+'
END AS bucket,
COUNT(1)
FROM data
GROUP BY 1