mysql字符串按数字排序、mysql字符串百分比排序

海哥  金牌会员 | 2024-12-4 18:48:13 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题

主题 826|帖子 826|积分 2478

一、媒介

在实际项目中,我们可能会遇到数据库中用字符串类型存储了数字、百分比等数据。若直接用varchar举行排序可能并不是我们想要的结果,以下整理一下字符串如何按数字举行排序。
Mysql提供了两种类型转换函数,可以将字符串转换为实行类型(如数字类型)。相关内容参考Mysql convert函数、 Mysql cast函数
示例数据:
  1. CREATE TABLE `test` (
  2.   `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT comment '主键id',
  3.   `number` varchar(10)  DEFAULT NULL COMMENT '数字',
  4.   `percent` varchar(10)  DEFAULT NULL COMMENT '百分比'
  5. ) ENGINE=InnoDB AUTO_INCREMENT=1;
  6. insert into test(`number`, percent) values
  7. ('89.01','89.01%'),
  8. ('89.10','89.10%'),
  9. ('100.01','100.01%');
  10. select * from test  order by `number` desc;
复制代码

可以发现数字最大的却排在最下面。
二、mysql字符串按数字排序

对上面的示例中的number字段举行排序
  1. # 方法一:将number字段转换为float类型。 需mysql版本8.0.17及以上
  2. select * from test order by cast(number as float) desc
  3. select * from test order by convert(number, float) desc
  4. # 方法二:将number字段转换为double类型。 需mysql版本8.0.17及以上
  5. select * from test order by cast(number as double) desc
  6. select * from test order by convert(number, double) desc
  7. # 方法三:将number字段转换为decimal类型
  8. select * from test order by cast(number as decimal(10,2)) desc
  9. select * from test order by convert(number, decimal(10,2)) desc
复制代码
排序结果如下:

若字段中全是整数无小数点,也可以利用下面的方式举行排序
  1. # 将number字段转换为整数类型
  2. select * from test order by cast(number as SIGNED) desc
  3. select * from test order by convert(number, SIGNED) desc
复制代码
三、mysql字符串百分比排序

对上面的示例中的percent字段举行排序
  1. # 方法一:将number字段转换为decimal类型
  2. -- 经测试后发现转换成decimal的时候默认会把%去掉,不受影响,若有影响可使用下面的方式,先把%去掉
  3. select * from test order by cast(percent as decimal(10,2)) desc
  4. select * from test order by convert(percent , decimal(10,2)) desc
  5. # 方法二:先手动替换掉多余的字符
  6. select * from test order by cast(REPLACE(percent, '%', '') as decimal(10,2)) desc
  7. select * from test order by convert(REPLACE(percent, '%', '') , decimal(10,2)) desc
复制代码
在上面的sql中:


  • REPLACE(percent, '%', '') :移除百分比字符串中的百分号。
  • CAST(... AS DECIMAL(10,2)) “”将结果转换为DECIMAL类型,其中10是总位数(包括小数点),2是小数位数。你可以根据需要调整这些值。
四、从字符串中提取数字并排序

1.如下,按文件名中的数字举行排序
让我们先看几个 file_name的示例值:


  • 中文_1.mp4
  • 中文_12.mp4
  • 中文_2.mp4
  • 中文_10.mp4
默认按file_name升序排序如下:

数据准备
  1. CREATE TABLE `test1` (
  2.   `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT comment '主键id',
  3.   `file_name` varchar(10)  DEFAULT NULL COMMENT '文件名'
  4. ) ENGINE=InnoDB AUTO_INCREMENT=1;
  5. insert into test1(file_name) values
  6. ('中文_1.txt'),
  7. ('中文_2.txt'),
  8. ('中文_10.txt'),
  9. ('中文_12.txt');
复制代码
利用 REGEXP_SUBSTR 函数提取并排序
在MySQL 8.0及以上版本中,我们可以利用 REGEXP_SUBSTR() 函数来提取字符串中的数字部分。这个函数允许我们利用正则表达式来指定我们想要匹配的模式。在这个例子中,我们利用正则表达式 \d+ 来匹配一个或多个数字。
以下是完整的SQL查询,用于实现按数字排序:
  1. SELECT *
  2. FROM test1
  3. WHERE file_name LIKE '%中文%'
  4. ORDER BY CAST(REGEXP_SUBSTR(file_name , '\\d+') AS UNSIGNED);
复制代码
这条SQL语句做了以下几件事:


  • WHERE original_name LIKE '%中文%':筛选出所有文件名包含“中文”的记录。
  • REGEXP_SUBSTR(original_name, '\\d+'):从 original_name 中提取第一组连续的数字。
  • CAST(... AS UNSIGNED):将提取出的字符串转换成无符号整数,以便按数字举行排序。
排序结果如下:


免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x
回复

使用道具 举报

0 个回复

正序浏览

快速回复

您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

海哥

金牌会员
这个人很懒什么都没写!

标签云

快速回复 返回顶部 返回列表