如果您的列具有varchar数据类型,则可以使用以下语法-select yourColumnName FROM yourTableName ORDER BY yourColumnName +0 DESC;
为了理解上述语法,让我们创建一个表。创建表的查询如下-mysql> create table selectOrderdemo
-> (
-> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name varchar(100),
-> RankNumber varchar(100)
-> );
使用insert命令在表中插入一些记录。查询如下-mysql> insert into selectOrderdemo(Name,RankNumber) values('Larry',-100);
mysql> insert into selectOrderdemo(Name,RankNumber) values('John',50);
mysql> insert into selectOrderdemo(Name,RankNumber) values('Bob',0);
mysql> insert into selectOrderdemo(Name,RankNumber) values('Carol',-110);
mysql> insert into selectOrderdemo(Name,RankNumber) values('David',98);
这是查询的选择顺序,其作用类似于数字。
情况1-如果您希望结果按降序排列,查询如下-mysql> select RankNumber FROM selectOrderdemo ORDER BY RankNumber+0 DESC;
这是输出-+------------+
| RankNumber |
+------------+
| 98 |
| 50 |
| 0 |
| -100 |
| -110 |
+------------+
5 rows in set (0.00 sec)
情况2-如果您希望结果按升序排列,查询如下-mysql> select RankNumber FROM selectOrderdemo ORDER BY RankNumber+0;
这是输出-+------------+
| RankNumber |
+------------+
| -110 |
| -100 |
| 0 |
| 50 |
| 98 |
+------------+
5 rows in set (0.00 sec)
情况3-如果要所有列,请使用以下查询-mysql> select * FROM selectOrderdemo ORDER BY RankNumber+0 DESC;
这是输出-+----+-------+------------+
| Id | Name | RankNumber |
+----+-------+------------+
| 5 | David | 98 |
| 2 | John | 50 |
| 3 | Bob | 0 |
| 1 | Larry | -100 |
| 4 | Carol | -110 |
+----+-------+------------+
5 rows in set (0.00 sec)