排序问题! 一个字段 例如 qwe001 sed002 erwer003 我怎么写一个sql语句然他们能按照 后面的数字大小排列起来 ! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 升序:select * from tb_name order by right(字段,3) 降序:select * from tb_name order by right(字段,3) desc 如果是: 记录数出现如:xx0011,xx00101呢? SELECT *,CAST(REVERSE(CAST(CONCAT('1',REVERSE(字段)) AS DECIMAL)) AS DECIMAL) As x FROM 表名 Order By x; CONCAT加个1是为了防止末尾是0而出错。 结果怎么都是这样的?方法1: select * from tb order by mid(title,3)+0 asc;方法2SELECT *,CAST(REVERSE(CAST(CONCAT('1',REVERSE(title)) AS DECIMAL)) AS DECIMAL) As x FROM tb Order By x;+----+--------+--------------+------+| id | title | detail | x |+----+--------+--------------+------+| 1 | tit001 | aaaaaaaaaa | 0.00 || 2 | ttd002 | bbbbbbbbb | 0.00 || 3 | tdt003 | cccccccccccc | 0.00 || 4 | adc012 | ddddddddddd | 0.00 || 5 | ttt010 | eeeeeeee | 0.00 |+----+--------+--------------+------+ 基于你的测试数据,应该改成这样了:select * from tb order by mid(title,4)+0 asc; 上面的是隐性转换或直接显性转换:select * from tb order by cast(right(title,char_length(title)-3) as unsigned int) asc; 奇怪啊:mysql> SELECT id,title,CAST(REVERSE(CAST(CONCAT('1',REVERSE(title)) AS DECIMAL)) AS DECIMAL) As x FROM mytable Order By x;+----+---------+-----+| id | title | x |+----+---------+-----+| 2 | tit001 | 11 || 1 | ttd002 | 21 || 3 | tdt003 | 31 || 5 | ttt010 | 101 || 6 | abcde11 | 111 || 4 | adc012 | 121 |+----+---------+-----+6 rows in set (0.00 sec)不知道为什么我的“AS DECIMAL”能转出整数,你的却是小数? 求大神们帮忙,MYSQL启动不了了。 用mysqldump备份完数据库后如何处理ibdata1文件 如何删除数据库名为"database"或"taa;"的数据库 如何使用mysql 求mysql.Dll版本号为5.0.7.0的程序集 新手提问 从列到行 请问这个建立存储过程的语句哪出错了? 请问支持mysql6.0的图形界面工具有哪些?谢谢! 在WIN2000上,用phpadmin连接mysql的问题,请看看吧 mysql查询结果集的设置 关于联合多表按日期查询,求教,谢谢各位
select * from tb_name order by right(字段,3) 降序:select * from tb_name order by right(字段,3) desc
方法2SELECT *,CAST(REVERSE(CAST(CONCAT('1',REVERSE(title)) AS DECIMAL)) AS DECIMAL) As x FROM tb Order By x;
+----+--------+--------------+------+
| id | title | detail | x |
+----+--------+--------------+------+
| 1 | tit001 | aaaaaaaaaa | 0.00 |
| 2 | ttd002 | bbbbbbbbb | 0.00 |
| 3 | tdt003 | cccccccccccc | 0.00 |
| 4 | adc012 | ddddddddddd | 0.00 |
| 5 | ttt010 | eeeeeeee | 0.00 |
+----+--------+--------------+------+
select * from tb order by mid(title,4)+0 asc;
或直接显性转换:select * from tb order by cast(right(title,char_length(title)-3) as unsigned int) asc;
mysql> SELECT id,title,CAST(REVERSE(CAST(CONCAT('1',REVERSE(title)) AS DECIMAL))
AS DECIMAL) As x FROM mytable Order By x;
+----+---------+-----+
| id | title | x |
+----+---------+-----+
| 2 | tit001 | 11 |
| 1 | ttd002 | 21 |
| 3 | tdt003 | 31 |
| 5 | ttt010 | 101 |
| 6 | abcde11 | 111 |
| 4 | adc012 | 121 |
+----+---------+-----+
6 rows in set (0.00 sec)不知道为什么我的“AS DECIMAL”能转出整数,你的却是小数?