【Oracle11g SQL详解】ORDER BY 子句的排序规则与应用

ORDER BY 子句的排序规则与应用

在 Oracle 11g 中，ORDER BY 子句用于对查询结果进行排序。通过使用 ORDER BY，可以使返回的数据按照指定的列或表达式以升序或降序排列，便于数据的分析和呈现。本文将详细讲解 ORDER BY 子句的规则及其常见应用。

一、ORDER BY 子句的基本语法

SELECT 列名1, 列名2, ...
FROM 表名
[WHERE 条件]
ORDER BY 列名 [ASC|DESC], 列名2 [ASC|DESC];

说明：

• ORDER BY：指定排序列或表达式。
• ASC（默认）：升序排列，从小到大。
• DESC：降序排列，从大到小。
• 可以根据多个列进行排序，按列顺序依次排序。

二、ORDER BY 子句的排序规则

1. 按单列排序
   • 默认情况下，ORDER BY 按升序排列。
   • 如果需要降序排列，必须显式指定 DESC。

示例：

SELECT employee_id, salary 
FROM employees
ORDER BY salary ASC;  -- 按薪资升序排列

SELECT employee_id, salary 
FROM employees
ORDER BY salary DESC; -- 按薪资降序排列

2. 按多列排序
   • 先按第一列排序，如果第一列值相同，则按第二列排序，以此类推。

示例：

SELECT employee_id, department_id, salary 
FROM employees
ORDER BY department_id ASC, salary DESC;

说明：

• 按 department_id 升序排列。
• 在 department_id 相同的情况下，按 salary 降序排列。

3. 按表达式排序
   • ORDER BY 支持基于表达式的排序，如计算列或函数结果。

示例：

SELECT employee_id, first_name, last_name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

说明： 按员工年薪（salary * 12）从高到低排序。

4. 按列别名排序
   • 可以使用 SELECT 中定义的列别名进行排序。

示例：

SELECT employee_id, first_name || ' ' || last_name AS full_name, salary 
FROM employees
ORDER BY full_name ASC;

说明： 按员工全名（full_name）字母顺序升序排列。

5. 按列位置排序
   • 直接使用列的位置编号排序，从 SELECT 子句中列的顺序开始。

示例：

SELECT employee_id, department_id, salary 
FROM employees
ORDER BY 2 ASC, 3 DESC;

说明：

• 2 表示按 department_id 升序排列。
• 3 表示按 salary 降序排列。

三、ORDER BY 与 NULL 值的排序

在 Oracle 中，NULL 值在排序时具有以下特点：

• 默认情况下，升序排列时，NULL 值排在最后；
• 降序排列时，NULL 值排在最前。

如果需要修改这一行为，可以使用 NULLS FIRST 或 NULLS LAST 明确指定。

示例：

SELECT employee_id, salary 
FROM employees
ORDER BY salary ASC NULLS FIRST; -- NULL 值排在最前

SELECT employee_id, salary 
FROM employees
ORDER BY salary DESC NULLS LAST; -- NULL 值排在最后

四、ORDER BY 子句的高级用法

1. 按日期排序

SELECT employee_id, hire_date 
FROM employees
ORDER BY hire_date DESC;

说明： 按雇佣日期从最近到最早排序。

2. 按条件排序

SELECT employee_id, salary, 
       CASE 
           WHEN salary >= 10000 THEN '高薪' 
           WHEN salary >= 5000 THEN '中等薪资' 
           ELSE '低薪' 
       END AS salary_level
FROM employees
ORDER BY salary_level ASC, salary DESC;

说明：

• 先按自定义薪资级别排序（高薪 > 中等薪资 > 低薪）。
• 在相同薪资级别内，再按实际薪资降序排列。

3. 限制查询结果的排序行数
   结合 ROWNUM 或 FETCH 子句，按排序结果筛选出前 N 条记录。

示例：返回薪资最高的前 5 名员工：

SELECT employee_id, salary 
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

五、性能优化建议

1. 使用索引

   • 对排序列建立索引，尤其是对大表或排序操作频繁的列。

2. 避免过多排序列

   • 排序列越多，排序计算量越大，尽量减少不必要的排序列。

3. 减少复杂表达式

   • 如果排序基于复杂表达式，建议在查询之前先将表达式计算好的结果存储为列。

六、练习示例

1. 查询所有员工信息，按部门编号升序排列：

SELECT * FROM employees
ORDER BY department_id ASC;

2. 查询所有员工的姓名和薪资，按薪资降序排列，薪资相同的按姓名升序排列：

SELECT first_name, last_name, salary 
FROM employees
ORDER BY salary DESC, first_name ASC;

3. 查询所有部门信息，按部门名称的字母倒序排列：

SELECT department_id, department_name 
FROM departments
ORDER BY department_name DESC;

七、小结

ORDER BY 子句是 SQL 查询中重要的工具，用于对数据进行排序以满足各种分析需求。掌握其语法、规则和优化方法，可以显著提升查询的实用性和效率。