如果表字段的类型为varchar型,但是里面存储的是纯数字,怎么实现按照数字的大小来排序?
下面的方法对于mysql和oracle都实用
order by 字段+0
order by 字段*1
等等都可以实现
例子:
Mysql:
mysql> select * from goolen;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 10 |
| 11 |
| 20 |
| 22 |
+------+
9 rows in set (0.00 sec)
mysql> select * from goolen order by id;
+------+
| id |
+------+
| 1 |
| 10 |
| 11 |
| 2 |
| 20 |
| 22 |
| 3 |
| 4 |
| 5 |
+------+
9 rows in set (0.00 sec)
mysql> select * from goolen order by id+0;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 10 |
| 11 |
| 20 |
| 22 |
+------+
9 rows in set (0.00 sec)
Oracle:
SQL> select * from goolen;
ID
------------------------------
20
44
28
15
29
3
25
39
51
26
10 rows selected.
SQL> select * from goolen order by id;
ID
------------------------------
15
20
25
26
28
29
3
39
44
51
10 rows selected.
SQL> select * from goolen order by id+0;
ID
------------------------------
3
15
20
25
26
28
29
39
44
51
10 rows selected.
SQL> select * from goolen order by id*1;
ID
------------------------------
3
15
20
25
26
28
29
39
44
51
10 rows selected.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23249684/viewspace-1251918/,如需转载,请注明出处,否则将追究法律责任。