如何在MySQL中对保存为VARCHAR的十进制数进行排序

vohkndzv 于 2023-03-07 发布在 Mysql

关注(0)|答案(1)|浏览(109)

不幸的是，我遇到了一个问题，我真的不知道如何解决它。
我在MySQL数据库中以VARCHAR格式存储了一些数字（无序）：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15. 1 15. 2 15. 3 15. 4 15. 5 15. 6 15. 7 15. 8 15. 9 15. 10 15. 11 15 16 17 18
谁能告诉我如何排序他们使用SQL查询在下面的方式？
结果应该是：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 15. 1 15. 2 15. 3 15. 4 15. 5 15. 6 15. 7 15. 8 15. 9 15. 10 15. 11 16 17
不幸的是，我已经尝试了我能想到的一切。
例如

SELECT meetings_points.number_of_points  
FROM meetings_points 
WHERE (`meetings_points`.`meeting` = 1026) 
ORDER BY 
  CAST(SUBSTRING_INDEX(meetings_points.number_of_points, '.', 1) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(meetings_points.number_of_points, '.', -1), '.', 1) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(meetings_points.number_of_points, '.', -1) AS UNSIGNED),
  meetings_points.number_of_points ASC;

不幸的是，它返回了一个不正确的结果：
1 2 3 4 5 6 7 8 9 10 11 12 1314 15. 1 15. 2 15. 3 15. 4 15. 5 15. 6 15. 7 15. 8 15. 9 15. 10 15. 11 1516 17 18
有谁知道怎么把这些数字排序吗？
非常感谢
我试过这个SQL（全错了）
x一个一个一个一个一个x一个一个二个一个x一个一个三个一个x一个x一个x一个x一个x一个x一个

mysql

来源：https://stackoverflow.com/questions/75596228/how-sort-decimal-numbers-in-mysql-saved-as-varchar

1条答案

按热度按时间

ycl3bljg1#

You can use the like operator within a case statement to sort the data as you described.

Extract main as the string before the '.' char and convert it to unsigned int.
If the string has a '.' char, extract suffix as the string after the '.' char and convert it to unsigned int, otherwise set suffix as 0.
Sort the data by (main, suffix)
Here is the query:

SELECT number_of_points,
       CAST(Substring_index(number_of_points, '.', 1) AS UNSIGNED) AS main,
       (CASE
            WHEN number_of_points LIKE '%.%' THEN
                CAST(Substring_index(number_of_points, '.', -1) AS UNSIGNED)
            ELSE
                0
        END
       ) AS suffix
FROM meetings_points
WHERE meeting = 1026
ORDER BY CAST(Substring_index(number_of_points, '.', 1) AS UNSIGNED),
         (CASE
              WHEN number_of_points LIKE '%.%' THEN
                  CAST(Substring_index(number_of_points, '.', -1) AS UNSIGNED)
              ELSE
                  0
          END
         )

number_of_points	main	suffix
1	1	0
2	2	0
3	3	0
4	4	0
5	5	0
6	6	0
7	7	0
8	8	0
9	9	0
10	10	0
11	11	0
12	12	0
13	13	0
14	14	0
15	15	0
15.1	15	1
15.2	15	2
15.3	15	3
15.4	15	4
15.5	15	5
15.6	15	6
15.7	15	7
15.8	15	8
15.9	15	9
15.10	15	10
15.11	15	11
16	16	0
17	17	0
18	18	0

赞(0）回复(0）举报 2023-03-07

我来回答

如何在MySQL中对保存为VARCHAR的十进制数进行排序

1条答案

相关问题

热门标签

最新问答