--- title: 编组 description: 排序查询 ORDER BY 子句用于对查询结果按照一个或多个属性列进行升序（ASC）或降序（DESC）排列，默认为升序。 排序查询语法 ORDER BY 支持单字段排序、多字段排序、按别名排序和按函数排序，多字段排序之间用逗号隔开。排序查询的 SQL 语句格式如下： SELECT select_list FROM t… --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.0.0 企业版 # 排序查询 更新时间：2026-05-28 09:09:07 ORDER BY 子句用于对查询结果按照一个或多个属性列进行升序（ASC）或降序（DESC）排列，默认为升序。 ## 排序查询语法 `ORDER BY` 支持单字段排序、多字段排序、按别名排序和按函数排序，多字段排序之间用逗号隔开。排序查询的 SQL 语句格式如下： ```sql SELECT select_list FROM table_list [WHERE query_condition] ORDER BY column_name [ASC|DESC] [,column_name [ASC|DESC]...]; ``` ## 排序查询示例 ### 单字段排序查询 按照 `deptno` 升序显示员工信息。 ```sql 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` 降序显示员工信息。 ```sql 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` 降序显示员工信息。 ```sql 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 ``` ### 按别名排序查询 ```sql 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()` 属于日期函数，可以获取对应日期中的年份。 ```sql 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()` 属于日期函数，可以获取对应日期中的年份。 ```sql 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` 子句可以在排序前对数据进行筛选。示例如下： ```sql 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 ``` 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线