Oracle查询排序需求：调整Plant_name列排序结果

这问题我之前处理过！默认的字符串排序是基于ASCII码值的，数字字符的ASCII比字母小，所以才会出现数字开头的Plant_name排在前面的情况。要实现你想要的[AA AB CA CB HG 1_A 1_G 1_H]顺序，核心是先把数据分成「字母开头」和「数字开头」两组，让字母组优先排序，再在各自组内按原规则排序。

通用解决方案（适配多数数据库） #

使用CASE表达式给不同开头的字符串分配排序优先级，字母开头的优先级设为1（先排），数字开头的设为2（后排），再在同一优先级内按Plant_name本身排序：

SELECT DISTINCT Plant_name
FROM table_plants
ORDER BY
  -- 先判断是否为字母开头，分配优先级
  CASE WHEN Plant_name REGEXP '^[A-Za-z]' THEN 1 ELSE 2 END,
  -- 同一优先级内按原字段排序
  Plant_name;

针对特定数据库的优化写法 #

MySQL/MariaDB #

可以用LEFT函数配合ASCII函数直接判断首字符范围，性能表现可能更优：

SELECT DISTINCT Plant_name
FROM table_plants
ORDER BY
  CASE WHEN ASCII(LEFT(Plant_name, 1)) BETWEEN ASCII('A') AND ASCII('Z') 
        OR ASCII(LEFT(Plant_name, 1)) BETWEEN ASCII('a') AND ASCII('z')
       THEN 1 ELSE 2 END,
  Plant_name;

PostgreSQL #

PostgreSQL支持用~操作符替代REGEXP，写法更简洁：

SELECT DISTINCT Plant_name
FROM table_plants
ORDER BY
  CASE WHEN Plant_name ~ '^[A-Za-z]' THEN 1 ELSE 2 END,
  Plant_name;

SQL Server #

SQL Server用LIKE语法判断首字符类型：

SELECT DISTINCT Plant_name
FROM table_plants
ORDER BY
  CASE WHEN Plant_name LIKE '[A-Za-z]%' THEN 1 ELSE 2 END,
  Plant_name;

执行以上任意语句后，就能得到你期望的排序结果：AA AB CA CB HG 1_A 1_G 1_H。

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