如何在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一个

ycl3bljg

ycl3bljg1#

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

  1. Extract main as the string before the '.' char and convert it to unsigned int.
  2. 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.
  3. 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_pointsmainsuffix
110
220
330
440
550
660
770
880
990
10100
11110
12120
13130
14140
15150
15.1151
15.2152
15.3153
15.4154
15.5155
15.6156
15.7157
15.8158
15.9159
15.101510
15.111511
16160
17170
18180

相关问题