SUBSTRING_INDEX 用符号和字符对数字进行排序 [英] SUBSTRING_INDEX sort the number with the symbol and the character

问题描述

我使用 MySQL 5.5 进行查询.我面临的问题是在字符前面用 - 对数字进行排序.下面是我的示例表，我想使用条件对 number_with_name 列进行排序:

I am used MySQL 5.5 to do my query. I am facing the problem to sort the number with - in front of the character. Below is my example table and I want to sort the column number_with_name with the condition:

表名:test123

+-----+--------------------+
| id  | number_with_name   |
+-----+--------------------+    
| 1   |   200-2 David      |
| 2   |   200-2-2 Peter    |
| 3   |   200-2-2-9 James  |
| 4   |   200 Robert       |
| 5   |   200-2-3 Siva     |
| 6   |   200-2-5 Denny    |
| 8   |   200-2-9 Rose     |
| 9   |   200-3 Kiki       |
| 10  |   100-3-2 Viva     |
| 11  |   100-3-15 Proton  |
| 12  |   100-3-6 Saga     |
| 13  |   100 Liver        |
| 14  |   100-3 Shawn      |
| 15  |   100-3-5-1 Kola   |
| 16  |   100-3-5-8 Frankie|
| 17  |   100-3-5 Jala     |
+----+---------------------+

我想要如下表所示的预期结果:

I want the expected result like below the table:

+-----+--------------------+
| id  | number_with_name   |
+-----+--------------------+  
| 13  |   100 Liver        |
| 14  |   100-3 Shawn      |
| 10  |   100-3-2 Viva     |
| 17  |   100-3-5 Jala     |
| 15  |   100-3-5-1 Kola   |
| 16  |   100-3-5-8 Frankie|
| 12  |   100-3-6 Saga     |
| 11  |   100-3-15 Proton  |
| 4   |   200 Robert       |
| 1   |   200-2 David      |
| 2   |   200-2-2 Peter    |
| 3   |   200-2-2-9 James  |
| 5   |   200-2-3 Siva     |
| 6   |   200-2-5 Denny    |
| 8   |   200-2-9 Rose     |
| 9   |   200-3 Kiki       |
+----+---------------------+

我用下面的 SQL 来排序，但是不行.

I have used below the SQL to sort, but it doesn't work.

SELECT * from test123 order by SUBSTRING_INDEX(number_with_name, '-', -1) + 0 asc

Akira 真题答案:

Akira answer result for real test:

希望有人可以指导我如何对这种情况进行排序.谢谢.

Hope someone can guide me on how to sort like this case. Thanks.

推荐答案

SELECT *
FROM test
ORDER BY SUBSTRING_INDEX(number_with_name, '-', 1) + 0
       , CASE WHEN LENGTH(number_with_name) - LENGTH(REPLACE(number_with_name, '-', '')) > 0
              THEN SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, '-', 2), '-', -1) + 0
              ELSE 0 
              END
       , CASE WHEN LENGTH(number_with_name) - LENGTH(REPLACE(number_with_name, '-', '')) > 1
              THEN SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, '-', 3), '-', -1) + 0
              ELSE 0 
              END

fiddle

这篇关于SUBSTRING_INDEX 用符号和字符对数字进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！