Poison

MySQL Sorting Rows

之前一直以为 MySQL 的「ORDER BY」关键字在多列排序时未显式声明排序方式的列会沿用最后显式声明排序方式的列,今天才发现自己弄错了,罪过罪过。在多列排序时,不同的列拥有不同的排序方式,如果未显式声明,则采用默认的升序排序方式。

You can sort on multiple columns, and you can sort different columns in different directions. For example, to sort by type of animal in ascending order, then by birth date within animal type in descending order (youngest animals first), use the following query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT name, species, birth FROM pet
-> ORDER BY species, birth DESC;
+----------+---------+------------+
| name | species | birth |
+----------+---------+------------+
| Chirpy | bird | 1998-09-11 |
| Whistler | bird | 1997-12-09 |
| Claws | cat | 1994-03-17 |
| Fluffy | cat | 1993-02-04 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
| Buffy | dog | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim | snake | 1996-04-29 |
+----------+---------+------------+

The DESC keyword applies only to the column name immediately preceding it (birth); it does not affect the species column sort order.

MySQL 5.7 Reference Manual 4.3.4.4 Sorting Rows