SELECT查询结果排序

1.排序数据

使用SELECT 的order by子句可以对查询结果进行排序。

确保ORDER BY子句在SELECT语句的最后，否则会出现错误。

示例：

查询产品名称并以字母顺序排序：

mysql> SELECT prod_name from Products ORDER BY prod_name;  
+---------------------+
| prod_name           |
+---------------------+
| 12 inch teddy bear  |
| 18 inch teddy bear  |
| 8 inch teddy bear   |
| Bird bean bag toy   |
| Fish bean bag toy   |
| King doll           |
| Queen doll          |
| Rabbit bean bag toy |
| Raggedy Ann         |
+---------------------+
9 rows in set (0.00 sec)
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

以价格升序排序：

mysql> SELECT prod_name,prod_price from Products ORDER BY prod_price;  
+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| Fish bean bag toy   |       3.49 |
| Bird bean bag toy   |       3.49 |
| Rabbit bean bag toy |       3.49 |
| Raggedy Ann         |       4.99 |
| 8 inch teddy bear   |       5.99 |
| 12 inch teddy bear  |       8.99 |
| King doll           |       9.49 |
| Queen doll          |       9.49 |
| 18 inch teddy bear  |      11.99 |
+---------------------+------------+
9 rows in set (0.00 sec)
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

价格降序排序：

mysql> SELECT prod_name,prod_price FROM  Products ORDER BY prod_price DESC;  
+---------------------+------------+
| prod_name           | prod_price |
+---------------------+------------+
| 18 inch teddy bear  |      11.99 |
| King doll           |       9.49 |
| Queen doll          |       9.49 |
| 12 inch teddy bear  |       8.99 |
| 8 inch teddy bear   |       5.99 |
| Raggedy Ann         |       4.99 |
| Fish bean bag toy   |       3.49 |
| Bird bean bag toy   |       3.49 |
| Rabbit bean bag toy |       3.49 |
+---------------------+------------+
9 rows in set (0.00 sec)
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2.按多个列排序

按多个列排序的时候，先按第一列排序，第一列中有相同值，相同值的记录再按第二列排序；如果第一列都是唯一的，则不再按第二列排序。如下示例：

mysql> SELECT prod_id,prod_price,prod_name FROM  Products ORDER BY prod_price,prod_name;  
+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BNBG02  |       3.49 | Bird bean bag toy   |
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG03  |       3.49 | Rabbit bean bag toy |
| RGAN01  |       4.99 | Raggedy Ann         |
| BR01    |       5.99 | 8 inch teddy bear   |
| BR02    |       8.99 | 12 inch teddy bear  |
| RYL01   |       9.49 | King doll           |
| RYL02   |       9.49 | Queen doll          |
| BR03    |      11.99 | 18 inch teddy bear  |
+---------+------------+---------------------+
9 rows in set (0.00 sec)
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

上面查询先依据价格排序，价格相同的按prod_name字典顺序排序。 
也可以使用位置代替排序列名，上述sql语句还可以这样写：

mysql> SELECT prod_id,prod_price,prod_name FROM  Products ORDER BY 2,3;  
 
 
1
 
 
1

使用位置代表列名，优点是不用重新输入列名，缺点是列名更改时会造成错误。

升序和降序

ORDER BY查询默认是升序排序，降序可以加上DESC,比如按价格由高到低排序：

mysql> SELECT prod_id,prod_price,prod_name FROM  Products ORDER BY prod_price DESC,prod_name;  
+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BR03    |      11.99 | 18 inch teddy bear  |
| RYL01   |       9.49 | King doll           |
| RYL02   |       9.49 | Queen doll          |
| BR02    |       8.99 | 12 inch teddy bear  |
| BR01    |       5.99 | 8 inch teddy bear   |
| RGAN01  |       4.99 | Raggedy Ann         |
| BNBG02  |       3.49 | Bird bean bag toy   |
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG03  |       3.49 | Rabbit bean bag toy |
+---------+------------+---------------------+
9 rows in set (0.00 sec)
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

DESC只对在它前面的一个列名有作用，MySQL> SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price,prod_name DESC;对prod_price无效：

mysql> SELECT prod_id,prod_price,prod_name FROM  Products ORDER BY prod_price,prod_name DESC;  
+---------+------------+---------------------+
| prod_id | prod_price | prod_name           |
+---------+------------+---------------------+
| BNBG03  |       3.49 | Rabbit bean bag toy |
| BNBG01  |       3.49 | Fish bean bag toy   |
| BNBG02  |       3.49 | Bird bean bag toy   |
| RGAN01  |       4.99 | Raggedy Ann         |
| BR01    |       5.99 | 8 inch teddy bear   |
| BR02    |       8.99 | 12 inch teddy bear  |
| RYL02   |       9.49 | Queen doll          |
| RYL01   |       9.49 | King doll           |
| BR03    |      11.99 | 18 inch teddy bear  |
+---------+------------+---------------------+
9 rows in set (0.00 sec)