需求

按版本号排序，版本号字段为字符类型，其中1.2.10应该大于1.2.9，但MySQL数据库会认为1.2.9大于1.2.10。

image.png

SQL解决方案一

思路是去掉版本号中的小数点，右边补零防止错误填写，类型转换为数字然后排序

  1. SELECT version_no ,
  2. CONCAT(
  3. LPAD( SUBSTRING_INDEX( SUBSTRING_INDEX( version_no, '.', 1 ), '.', - 1 ), 3, '0' ),
  4. LPAD( SUBSTRING_INDEX( SUBSTRING_INDEX( version_no, '.', 2 ), '.', - 1 ), 3, '0' ),
  5. LPAD(CASE WHEN LENGTH(SUBSTRING_INDEX( version_no, '.', 3 ))=LENGTH(SUBSTRING_INDEX( version_no, '.', 2 ))
  6. THEN '000'
  7. ELSE SUBSTRING_INDEX( SUBSTRING_INDEX( version_no, '.', 3 ), '.', - 1 ) END , 3, '0' ) 
  8. ) as vv
  9. from tb_version  order by vv desc;

image.png

SQL解决方案二

以小数点为分隔，取各个值，然后逐步按每个值排序

SELECT
    version_no,
SUBSTRING_INDEX(version_no,'.',1) AS first_version,
SUBSTRING_INDEX(SUBSTRING_INDEX(version_no,'.',-2),'.',1) AS second_version,
SUBSTRING_INDEX(version_no,'.',-1) AS third_version
FROM
    tb_version
order BY first_version+0 desc,
second_version+0 desc,
third_version+0 desc

image.png