时间戳

首页 > 解决方案 > 如何在 SQL 中按数字排序？

sql - 如何在 SQL 中按数字排序？

问题描述

我遇到了一个问题，即按照 SQL 中的列数据中的数字进行排序。例如，列数据包括 100-1/1/1 ABC、100-1/1/3 CDE、100-1/1/2 CDE。我想按订单号结果排序，如 100-1/1/1 ABC ,100-1/1/2 CDE 和 100-1/1/3 CDE。

以下是示例表数据，表名称为test2：

id  |    name          | 
4     200 DAVID
1     100 JOHN
3     100-1 SHAWN
9     100-1/1 PETER
11    100-1/1/1 ALVIS
12    100-1/1/10 ROBERT
2     100-1/1/11 HENRY
13    100-1/1/3 PIRES
14    100-2 CRISTY
20    100-1/2 BILLY
32    100-1/2/1 JIOUS

我用的是 MySQL 版本，写的 SQL 查询是SELECT * FROM test2 order by name，但是不能正确排序。

实际上我希望预期的结果如下表所示：

id  |    name          | 
1     100 JOHN
3     100-1 SHAWN
9     100-1/1 PETER
11    100-1/1/1 ALVIS
13    100-1/1/3 PIRES
12    100-1/1/10 ROBERT
2     100-1/1/11 HENRY
20    100-1/2 BILLY
32    100-1/2/1 JIOUS
14    100-2 CRISTY
4     200 DAVID

这是我对名称进行排序的真实场景：

图片

希望有人能指导我解决这个问题。谢谢。

标签： sql

解决方案

我不是使用正则表达式的专家，但是使用基本的表达式我们可以实现它，如下所示，

with cte as
(
select '200 DAVID' name
union all
select '100 JOHN'
union all
select '100-1 SHAWN'
union all
select '100-1/1 PETER'
union all
select '100-1/1/1 ALVIS'
union all
select '100-1/1/3 PIRES'
union all
select '100-1/1/10 ROBERT'
union all
select '100-1/1/11 HENRY'
union all
select '100-1/2 BILLY'
union all
select '100-1/2/1 JIOUS'
)
select *
  from(select t.*,cast(regexp_substr(name,'[0-9]+') as unsigned) col1
                 ,cast(regexp_substr(name,'[0-9]+',1,2) as unsigned) col2
                 ,cast(regexp_substr(name,'[0-9]+',1,3) as unsigned) col3
                 ,cast(regexp_substr(name,'[0-9]+',1,4) as unsigned) col4
  from cte t) c
order by col1,col2,col3,col4

首先，我们从字符串中提取排序所需的数字，使用regexp_substr(name,'[0-9]+')它匹配第一个数字一个字符并返回第一个匹配项，regexp_substr(name,'[0-9]+',1,2)第二个匹配项从另一个数字字符匹配等等......然后在按子句排序。

Demo

编辑：- MYSQL 旧版本的解决方案

select t.id,t.name
from
(
select t.*, cast((case when col1_col2_ref > 0 
                      then 
                        substring_index(modified_name,'-',1)
                  else 
                     modified_name
                  end) as unsigned) col1
          , cast((case when    col1_col2_ref > 0
                           and col3_ref > 0
                       then 
                          substr(modified_name,(col1_col2_ref + 1),(col3_ref - (col1_col2_ref + 1)))
                       when col1_col2_ref > 0
                       then 
                         substr(modified_name,(col1_col2_ref + 1))
                  end) as unsigned) col2
          , cast((case when    col3_ref > 0
                           and col4_ref > 0
                       then 
                         substr(modified_name,(col3_ref + 1),(col4_ref - (col3_ref + 1)))
                       when col3_ref > 0
                       then 
                         substr(modified_name,(col3_ref + 1))
                  end) as unsigned) col3
          , cast((case when col4_ref > 0
                        then 
                         substr(modified_name,(col4_ref + 1))
                   end) as unsigned) col4
  from
(
select t.*,substring_index(name,' ',1) modified_name
          ,locate('-',name,1) col1_col2_ref
          ,locate('/',name,1) col3_ref
          ,locate('/',name,locate('/',name,1)+1) col4_ref
  from test t
) t
) t
order by col1,col2,col3,col4

Demo2

推荐阅读