sql排序

发表于 | 分类于 ， |   热度
| 字数统计: 494 | 阅读时长 ≈ 2

select 排序的子句 order by
子句：sql语句由子句构成，有些子句是必须的，有些字句是可选的。一个子句通常由一个关键字加上所提供的数据组成。比如select中的from子句、select中的order by子句等。
order by 获取一个或多个列的名字，据此对输出进行排序

1
2
3
4
5
6
7
8
9
10
11
mysql> select id,model_cpu,num_cpus from device_info order by num_cpus limit 5; 
+----+----------------------------------+----------+ 
| id | model_cpu | num_cpus | 
+----+----------------------------------+----------+ 
| 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

1
2
3
4
5
6
7
8
9
10
11
mysql> select id,model_cpu,num_cpus from device_info order by num_cpus desc 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语句中查询的列的顺序排序：
按多个列排序 

1
2
3
4
5
6
7
8
9
10
11
mysql> select id,model_cpu,num_cpus from device_info order by num_cpus,id 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)

按列位置排序 

1
2
3
4
5
6
7
8
9
10
11
mysql> select id,model_cpu,num_cpus from device_info order by 3,1 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)
----------------本文结束 感谢阅读----------------