Mysql对含有数字的字符类型字段进行排序的解决方案(如:按照楼栋的楼号进行排序,"龙湖小区12号楼","龙湖小区06号楼","龙湖小区33号楼")

最新推荐文章于 2022-06-27 20:00:01 发布

DamonREN

最新推荐文章于 2022-06-27 20:00:01 发布

阅读量904

点赞数

分类专栏： Mysql 文章标签： Mysql mysql排序

本文链接：https://blog.csdn.net/DamonREN/article/details/82386908

版权

Mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

如排序前:

排序后:

解决方案:(思路是使用一个FUNCTION函数将此字段的数字取出,然后按照此数值进行排序)

(1).创建一个虚拟表:
select * from (
       select '龙湖小区12号楼' AS building_name UNION ALL
       select '龙湖小区06号楼' UNION ALL
       select '龙湖小区19号楼' UNION ALL
       select '龙湖小区33号楼' UNION ALL
       select '龙湖小区2号楼' UNION ALL
       select '龙湖小区66号楼'
   ) tb;

(2).创建mysql函数GetNum(field):

   DELIMITER $$

   CREATE FUNCTION GetNum (Varstring varchar(50))
   RETURNS varchar(30)
   BEGIN
   DECLARE v_length INT DEFAULT 0;
   DECLARE v_Tmp varchar(50) default '';
   set v_length=CHAR_LENGTH(Varstring);
   WHILE v_length > 0 DO
   IF (ASCII(mid(Varstring,v_length,1))>47 and ASCII(mid(Varstring,v_length,1))<58 ) THEN
   set v_Tmp=concat(v_Tmp,mid(Varstring,v_length,1));
   END IF;
   SET v_length = v_length - 1;
   END WHILE;
   RETURN REVERSE(v_Tmp);
   END$$

   DELIMITER ;

(3).调用GetNum(field)函数,并没有实现数字排序
select * from (
       select '龙湖小区12号楼' AS building_name UNION ALL
       select '龙湖小区06号楼' UNION ALL
       select '龙湖小区19号楼' UNION ALL
       select '龙湖小区33号楼' UNION ALL
       select '龙湖小区2号楼' UNION ALL
       select '龙湖小区66号楼'
   ) tb order by GetNum(building_name);

(4).原因是GetNum(field)得到的也是字符型的,所以要对目标字段+0实现排序.
select * from (
       select '龙湖小区12号楼' AS building_name UNION ALL
       select '龙湖小区06号楼' UNION ALL
       select '龙湖小区19号楼' UNION ALL
       select '龙湖小区33号楼' UNION ALL
       select '龙湖小区2号楼' UNION ALL
       select '龙湖小区66号楼'
   ) tb order by GetNum(building_name) + 0;

PS:对纯数字的字符类型字段进行排序:可以直接将字段*1或者+0可以将MySQL纯数值字符类型字段进行排序.