MySQL Order By子句
2019-10-16 22:57:58
在本教學中，您將學習如何使用MySQL ORDER BY子句來排序結果集。
1. MySQL ORDER BY子句簡介
當使用SELECT語句查詢表中的資料時，結果集不按任何順序進行排序。要對結果集進行排序，請使用ORDER BY子句。 ORDER BY子句允許：
- 對單個列或多個列排序結果集。
- 按升序或降序對不同列的結果集進行排序。
下面說明了ORDER BY子句的語法：
SELECT column1, column2,...
FROM tbl
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...
ASC表示升序，DESC表示降序。預設情況下，如果不明確指定ASC或DESC，ORDER BY子句會按照升序對結果集進行排序。
下面我們來學習和練習一些使用ORDER BY子句的例子。
2. MySQL ORDER BY範例
請參見範例資料庫(yiibaidb)中的customers表，customers表的結構如下所示 -
mysql> desc customers;
+------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| customerNumber | int(11) | NO | PRI | NULL | |
| customerName | varchar(50) | NO | | NULL | |
| contactLastName | varchar(50) | NO | | NULL | |
| contactFirstName | varchar(50) | NO | | NULL | |
| phone | varchar(50) | NO | | NULL | |
| addressLine1 | varchar(50) | NO | | NULL | |
| addressLine2 | varchar(50) | YES | | NULL | |
| city | varchar(50) | NO | | NULL | |
| state | varchar(50) | YES | | NULL | |
| postalCode | varchar(15) | YES | | NULL | |
| country | varchar(50) | NO | | NULL | |
| salesRepEmployeeNumber | int(11) | YES | MUL | NULL | |
| creditLimit | decimal(10,2) | YES | | NULL | |
+------------------------+---------------+------+-----+---------+-------+
13 rows in set
以下查詢從customers表中查詢聯絡人，並按contactLastname升序對聯絡人進行排序。
SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname;
執行上面查詢，得到以下結果 -
mysql> SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname;
+-----------------+------------------+
| contactLastname | contactFirstname |
+-----------------+------------------+
| Accorti | Paolo |
| Altagar,G M | Raanan |
| Andersen | Mel |
| Anton | Carmen |
| Ashworth | Rachel |
| Barajas | Miguel |
| Benitez | Violeta |
| Bennett | Helen |
| Berglund | Christina |
| Bergulfsen | Jonas |
| Bertrand | Marie |
... ....
| Young | Julie |
| Young | Mary |
| Young | Dorothy |
+-----------------+------------------+
122 rows in set
如果要按姓氏降序對聯絡人進行排序，請在ORDER BY子句中的contactLastname列後面指定DESC，如下查詢：
SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname DESC;
執行上面查詢，得到以下結果 -
mysql> SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname DESC;
+-----------------+------------------+
| contactLastname | contactFirstname |
+-----------------+------------------+
| Young | Jeff |
| Young | Julie |
| Young | Mary |
... ...
| Anton | Carmen |
| Andersen | Mel |
| Altagar,G M | Raanan |
| Accorti | Paolo |
+-----------------+------------------+
122 rows in set
如果要按姓氏按降序和名字按升序排序聯絡人，請在相應列中分別指定DESC和ASC，如下所示：
SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname DESC,
contactFirstname ASC;
執行上面查詢，得到以下結果 -
mysql> SELECT
contactLastname,
contactFirstname
FROM
customers
ORDER BY
contactLastname DESC,
contactFirstname ASC;
+-----------------+------------------+
| contactLastname | contactFirstname |
+-----------------+------------------+
| Young | Dorothy |
| Young | Jeff |
| Young | Julie |
| Young | Mary |
| Yoshido | Juri |
| Walker | Brydey |
| Victorino | Wendy |
| Urs | Braun |
| Tseng | Jerry |
... ...
| Brown | Julie |
| Brown | William |
| Bertrand | Marie |
| Bergulfsen | Jonas |
| Berglund | Christina |
| Bennett | Helen |
| Benitez | Violeta |
| Barajas | Miguel |
| Ashworth | Rachel |
| Anton | Carmen |
| Andersen | Mel |
| Altagar,G M | Raanan |
| Accorti | Paolo |
+-----------------+------------------+
122 rows in set
在上面的查詢中，ORDER BY子句首先按照contactLastname列降序對結果集進行排序，然後按照contactFirstname列升序對排序結果集進行排序，以生成最終結果集。
MySQL ORDER BY按表示式排序範例
ORDER BY子句還允許您根據表示式對結果集進行排序。請參閱以下orderdetails表結構 -
mysql> desc orderdetails;
+-----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| orderNumber | int(11) | NO | PRI | NULL | |
| productCode | varchar(15) | NO | PRI | NULL | |
| quantityOrdered | int(11) | NO | | NULL | |
| priceEach | decimal(10,2) | NO | | NULL | |
| orderLineNumber | smallint(6) | NO | | NULL | |
+-----------------+---------------+------+-----+---------+-------+
5 rows in set
以下查詢從orderdetails表中選擇訂單行記錄專案。它計算每個訂單項的小計，並根據訂單編號，訂單行號(orderLineNumber)和小計(quantityOrdered * priceEach)對結果集進行排序。
SELECT
ordernumber,
orderlinenumber,
quantityOrdered * priceEach
FROM
orderdetails
ORDER BY
ordernumber,
orderLineNumber,
quantityOrdered * priceEach;
執行上面語句，總共有 2996 行結果集，以下是部分結果集片斷 -
為了使查詢更易於閱讀，可以按列別名進行排序，方法如下：
SELECT
ordernumber,
orderlinenumber,
quantityOrdered * priceEach AS subtotal
FROM
orderdetails
ORDER BY
ordernumber,
orderLineNumber,
subtotal;
執行上面語句，總共有 2996 行結果集，以下是部分結果集片斷 -
上面表示式中，使用subtotal作為表示式quantityOrdered * priceEach的列別名，並根據小計別名(subtotal)對結果集進行排序。
MySQL ORDER BY與自定義排序順序
ORDER BY子句允許使用FIELD()函式為列中的值定義自己的自定義排序順序。
看看下面 orders表的結構如下所示 -
mysql> desc orders;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| orderNumber | int(11) | NO | PRI | NULL | |
| orderDate | date | NO | | NULL | |
| requiredDate | date | NO | | NULL | |
| shippedDate | date | YES | | NULL | |
| status | varchar(15) | NO | | NULL | |
| comments | text | YES | | NULL | |
| customerNumber | int(11) | NO | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
7 rows in set
例如，如果要按以下順序基於以下狀態的值對訂單進行排序：
In ProcessOn HoldCancelledResolvedDisputedShipped
可以使用FIELD()函式將這些值對映到數值列表，並使用數位進行排序; 請參閱以下查詢：
SELECT
orderNumber, status
FROM
orders
ORDER BY FIELD(status,
'In Process',
'On Hold',
'Cancelled',
'Resolved',
'Disputed',
'Shipped');
執行上面查詢語句，得到以下結果 -
mysql> SELECT
orderNumber, status
FROM
orders
ORDER BY FIELD(status,
'In Process',
'On Hold',
'Cancelled',
'Resolved',
'Disputed',
'Shipped');
+-------------+------------+
| orderNumber | status |
+-------------+------------+
| 10420 | In Process |
| 10421 | In Process |
| 10422 | In Process |
| 10423 | In Process |
| 10424 | In Process |
| 10425 | In Process |
| 10334 | On Hold |
| 10401 | On Hold |
| 10407 | On Hold |
| 10414 | On Hold |
| 10167 | Cancelled |
| 10179 | Cancelled |
| 10248 | Cancelled |
| 10253 | Cancelled |
| 10260 | Cancelled |
| 10262 | Cancelled |
| 10164 | Resolved |
| 10327 | Resolved |
... ...
| 10413 | Shipped |
| 10416 | Shipped |
| 10418 | Shipped |
| 10419 | Shipped |
+-------------+------------+
326 rows in set
在本教學中，我們使用了各種範例演示了如何使用MySQL ORDER BY子句對結果集進行排序。