sql排序

select 排序的子句 order by 

子句:sql语句由子句构成,有些子句是必须的,有些字句是可选的。一个子句通常由一个关键字加上说提供的数据组成。比如select中的from子句、select中的order by子句等。

order by 取一个或多个列的名字,据此对输出进行排序。如下:

mysql> select id,model_cpu,num_cpus from device_info <span style="color: #ff0000;">order by num_cpus</span> limit 5;
+----+----------------------------------+----------+
| id | model_cpu | <span style="color: #ff0000;">num_cpus</span> |
+----+----------------------------------+----------+
| 1 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
| 13 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
| 12 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
| 6 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
| 3 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
+----+----------------------------------+----------+
5 rows in set (0.00 sec)

默认的是从由小到大的次序进行排序,如果要从大到小的排序,加上关键字 desc

mysql> select id,model_cpu,num_cpus from device_info order by num_cpus <span style="color: #ff0000;">desc</span> limit 5;
+----+----------------------------------+----------+
| id | model_cpu | num_cpus |
+----+----------------------------------+----------+
| 24 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 24 |
| 18 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 24 |
| 17 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 24 |
| 15 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 24 |
| 14 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 24 |
+----+----------------------------------+----------+
5 rows in set (0.00 sec)

注意:order by 子句的位置必须是select 语句接着的下一条子句,子句的顺序不对将会报错。

order by子句可以按多个列排序,也可以按select语句中查询的列的顺序排序:

按多个列排序:

mysql> select id,model_cpu,num_cpus from device_info order by <span style="color: #ff0000;">num_cpus,id</span> limit 5;
+----+----------------------------------+----------+
| id | model_cpu | num_cpus |
+----+----------------------------------+----------+
| 1 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
| 3 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
| 6 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
| 12 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
| 13 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
+----+----------------------------------+----------+
5 rows in set (0.00 sec)

按列位置排序

mysql> select id,model_cpu,num_cpus from device_info order by <span style="color: #ff0000;">3,1</span> limit 5;
+----+----------------------------------+----------+
| id | model_cpu | num_cpus |
+----+----------------------------------+----------+
| 1 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
| 3 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
| 6 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
| 12 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
| 13 | Intel(R)Xeon(R)CPUE5-26200@2.00G | 8 |
+----+----------------------------------+----------+
5 rows in set (0.00 sec)

转载请注明出处:http://www.xiaomastack.com/2014/07/26/sqlpx/ 谢谢!

发表评论:

你的电子邮件地址将不会被公开.

4 × 4 =