以下是语法-select *from yourTableName order by yourColumnName*1,yourColumnName;
让我们首先创建一个表-mysql> create table DemoTable
(
Value varchar(100)
);
使用插入命令在表中插入一些记录-mysql> insert into DemoTable values('90');
mysql> insert into DemoTable values('86');
mysql> insert into DemoTable values('45');
mysql> insert into DemoTable values('85');
mysql> insert into DemoTable values('085');
mysql> insert into DemoTable values('090');
mysql> insert into DemoTable values('045');
使用select语句显示表中的所有记录-mysql> select *from DemoTable;
这将产生以下输出-+-------+
| Value |
+-------+
| 90 |
| 86 |
| 45 |
| 85 |
| 085 |
| 090 |
| 045 |
+-------+
7 rows in set (0.00 sec)
以下是对保存为VARCHAR的数字进行排序的查询,其中一些数字必须在0之前mysql> select *from DemoTable order by Value*1,Value;
这将产生以下输出-+-------+
| Value |
+-------+
| 045 |
| 45 |
| 085 |
| 85 |
| 86 |
| 090 |
| 90 |
+-------+
7 rows in set (0.00 sec)