排序查询

ORDER BY 子句用于对查询结果按照一个或多个属性列进行升序（ASC）或降序（DESC）排列，默认为升序。

排序查询语法

ORDER BY 支持单字段排序、多字段排序、按别名排序和按函数排序，多字段排序之间用逗号隔开。排序查询的 SQL 语句格式如下：

SELECT select_list FROM table_list 
[WHERE query_condition] 
ORDER BY column_name [ASC|DESC] [,column_name [ASC|DESC]...];

排序查询示例

单字段排序查询

按照 deptno 升序显示员工信息。

obclient> SELECT empname, deptno FROM emp ORDER BY deptno;
+---------+--------+
| empname | deptno |
+---------+--------+
| SMITH   |     20 |
| SCOTT   |     20 |
| ALLEN   |     30 |
| BLAKE   |     30 |
| JONES   |     40 |
| FORD    |     40 |
+---------+--------+
6 rows in set

按照 deptno 降序显示员工信息。

obclient> SELECT empname, deptno FROM emp ORDER BY deptno DESC;
+---------+--------+
| empname | deptno |
+---------+--------+
| JONES   |     40 |
| FORD    |     40 |
| ALLEN   |     30 |
| BLAKE   |     30 |
| SMITH   |     20 |
| SCOTT   |     20 |
+---------+--------+
6 rows in set

多字段排序查询

按照 deptno 升序、sal 降序显示员工信息。

obclient> SELECT empname, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;
+---------+--------+---------+
| empname | deptno | sal     |
+---------+--------+---------+
| SCOTT   |     20 | 3000.00 |
| SMITH   |     20 |  800.00 |
| BLAKE   |     30 | 2850.00 |
| ALLEN   |     30 | 1600.00 |
| FORD    |     40 | 3000.00 |
| JONES   |     40 | 2975.00 |
+---------+--------+---------+
6 rows in set

按别名排序查询

obclient> SELECT empname '员工姓名', deptno '部门编号', sal '薪酬' FROM emp ORDER BY 部门编号 ASC,薪酬 DESC;
+--------------+--------------+---------+
| 员工姓名     | 部门编号     | 薪酬    |
+--------------+--------------+---------+
| SCOTT        |           20 | 3000.00 |
| SMITH        |           20 |  800.00 |
| BLAKE        |           30 | 2850.00 |
| ALLEN        |           30 | 1600.00 |
| FORD         |           40 | 3000.00 |
| JONES        |           40 | 2975.00 |
+--------------+--------------+---------+
6 rows in set

按函数排序查询

如下示例在 ORDER BY 中使用了函数排序。其中 YEAR() 属于日期函数，可以获取对应日期中的年份。

obclient> SELECT empname, deptno, YEAR(hiredate) FROM emp ORDER BY deptno ASC, YEAR(hiredate) DESC;
+---------+--------+----------------+
| empname | deptno | YEAR(hiredate) |
+---------+--------+----------------+
| SCOTT   |     20 |           1987 |
| SMITH   |     20 |           1980 |
| ALLEN   |     30 |           1981 |
| BLAKE   |     30 |           1981 |
| JONES   |     40 |           1981 |
| FORD    |     40 |           1981 |
+---------+--------+----------------+
6 rows in set

如下示例在 ORDER BY 中使用了别名排序。其中 YEAR() 属于日期函数，可以获取对应日期中的年份。

obclient> SELECT empname '员工姓名', deptno '部门编号',YEAR (hiredate) '出生日期' FROM emp ORDER BY deptno ASC, 出生日期 DESC;
+--------------+--------------+--------------+
| 员工姓名     | 部门编号     | 出生日期     |
+--------------+--------------+--------------+
| SCOTT        |           20 |         1987 |
| SMITH        |           20 |         1980 |
| ALLEN        |           30 |         1981 |
| BLAKE        |           30 |         1981 |
| JONES        |           40 |         1981 |
| FORD         |           40 |         1981 |
+--------------+--------------+--------------+
6 rows in set

在 WHERE 子句之后排序

使用 WHERE 子句可以在排序前对数据进行筛选。示例如下：

obclient> SELECT empname, deptno, sal, YEAR(hiredate) FROM emp WHERE sal >= 1000 ORDER BY YEAR(hiredate);
+---------+--------+---------+----------------+
| empname | deptno | sal     | YEAR(hiredate) |
+---------+--------+---------+----------------+
| ALLEN   |     30 | 1600.00 |           1981 |
| JONES   |     40 | 2975.00 |           1981 |
| BLAKE   |     30 | 2850.00 |           1981 |
| FORD    |     40 | 3000.00 |           1981 |
| SCOTT   |     20 | 3000.00 |           1987 |
+---------+--------+---------+----------------+
5 rows in set