文章目录
按一列排序按多列排序指定排序方向:desclimit和order by组合总结
按一列排序
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
)
按多列排序
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
)
指定排序方向:desc
mysql
> select prod_id
, prod_price
, prod_name
-> from products
-> order by prod_price desc
;
+---------+------------+---------------------+
| 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
|
| BNBG01
| 3.49 | Fish bean bag toy
|
| BNBG02
| 3.49 | Bird bean bag toy
|
| BNBG03
| 3.49 | Rabbit bean bag toy
|
+---------+------------+---------------------+
9 rows in set
(0.00 sec
)
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
)
可见desc只作用于其前面的1列,而之前的distinct会作用于其后的所有列
limit和order by组合
选出最大值
mysql
> select prod_price
-> from products
-> order by prod_price desc
-> limit
1;
+------------+
| prod_price
|
+------------+
| 11.99 |
+------------+
1 row in set
(0.00 sec
)
返回最大的前4个
mysql
> select prod_price
-> from products
-> order by prod_price desc
-> limit
4;
+------------+
| prod_price
|
+------------+
| 11.99 |
| 9.49 |
| 9.49 |
| 8.99 |
+------------+
总结
order by子句必须在from子句后面;limit子句必须在order by子句后面