SQL中如何对字母数字型分桶字符串按数值逻辑正确排序

你不需要改动现有通过CASE生成分桶的主体逻辑，只需要补充ORDER BY子句，借助字符串函数提取每个分桶标签的起始数值作为排序依据，就能得到符合数值区间顺序的结果，完全不影响你现有返回的字段格式。

核心原理 #

字符串类型的分桶标签直接排序时，会按字符逐位做字典序比较，比如11-20会因为首字符是1排在6-10前面，不符合数值区间的逻辑。只要提取每个分桶标签里代表区间起点的数字，把它转成数值类型排序，就能得到正确顺序。

具体写法 #

通用兼容写法（适配多数SQL方言） #

针对你当前的分桶格式，只需要判断标签里的分隔符是-还是+，截取分隔符之前的数字部分转成整数排序即可，以MySQL为例的完整代码：

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
-- 新增以下排序逻辑即可，原有查询结构完全不动
ORDER BY CAST(
  CASE
    WHEN bucket LIKE '%-%' THEN SUBSTRING_INDEX(bucket, '-', 1)
    WHEN bucket LIKE '%+' THEN SUBSTRING_INDEX(bucket, '+', 1)
  END AS UNSIGNED
)

不同数据库只需要替换对应的字符串截取函数即可：

• PostgreSQL：用split_part(bucket, '-', 1)、split_part(bucket, '+', 1)替换SUBSTRING_INDEX部分
• SQL Server：用LEFT(bucket, CHARINDEX('-', bucket)-1)、LEFT(bucket, CHARINDEX('+', bucket)-1)替换对应截取逻辑

简洁正则写法（适配支持正则函数的现代SQL版本） #

如果你用的是MySQL 8.0+、PostgreSQL、BigQuery、SparkSQL等支持正则截取的数据库，可以直接用正则提取标签开头的连续数字作为排序依据，不需要额外判断分隔符，写法更简洁：

-- 排序逻辑替换成下面这行即可
ORDER BY CAST(REGEXP_SUBSTR(bucket, '^[0-9]+') AS UNSIGNED)

这个写法的逻辑是匹配字符串开头的所有连续数字，刚好对应每个分桶的区间起始值，不管后续分桶加了什么后缀格式，只要开头是区间起点数字，排序都不会出错。

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