Oracle 子查询详解

一、子查询的定义与分类

子查询是指嵌套在主查询中的 SELECT 语句，它可以在主查询执行前或执行过程中独立运行，其结果用于主查询的条件判断或数据获取。根据返回结果的行数和列数，可分为以下几类：

二、单行子查询（返回单一行一列）

特点：主查询条件中可直接使用比较运算符（=, >, < 等）
示例：查询薪资高于部门平均薪资的员工

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)FROM employeesWHERE department_id = 50
);

三、多行子查询（返回多行一列）

常用运算符：IN, ANY, ALL

IN：匹配子查询结果中的任意一个值
ANY：与子查询中的任意一个值比较
ALL：与子查询中的所有值比较

示例：查询与部门 100 职位相同的员工

SELECT employee_id, first_name, job_id
FROM employees
WHERE job_id IN (SELECT job_idFROM employeesWHERE department_id = 100
);

四、多列子查询（返回多行多列）

特点：主查询条件需同时匹配多个列的值
示例：查询与员工 King 薪资和部门相同的员工

SELECT employee_id, first_name, salary, department_id
FROM employees
WHERE (salary, department_id) = (SELECT salary, department_idFROM employeesWHERE last_name = 'King'
);

五、相关子查询（依赖主查询参数）

执行逻辑：主查询每处理一行，子查询就执行一次
示例：查询各部门薪资最高的员工

SELECT e.department_id, e.employee_id, e.first_name, e.salary
FROM employees e
WHERE salary = (SELECT MAX(salary)FROM employeesWHERE department_id = e.department_id
);

六、FROM 子句中的子查询（派生表）

场景：将子查询结果作为临时表参与主查询
示例：查询薪资前 10% 的员工

SELECT *
FROM (SELECT employee_id, first_name, salary,PERCENT_RANK() OVER (ORDER BY salary DESC) p_rankFROM employees
)
WHERE p_rank < 0.1;

Oracle 分页查询详解

一、ROWNUM 分页（Oracle 传统方法）

原理：通过伪列 ROWNUM 对查询结果编号，实现分页
基本语法：

SELECT *
FROM (SELECT t.*, ROWNUM rnFROM (SELECT * FROM table_name ORDER BY column) tWHERE ROWNUM <= :end_row
)
WHERE rn >= :start_row;

示例（查询第 11-20 条数据）：

SELECT *
FROM (SELECT e.*, ROWNUM rnFROM (SELECT * FROM employees ORDER BY employee_id) eWHERE ROWNUM <= 20
)
WHERE rn >= 11;

注意事项：

ROWNUM 必须从 1 开始连续编号，无法直接使用ROWNUM > 10
排序必须放在最内层查询，否则分页顺序可能错乱

二、ROW_NUMBER () 函数分页（Oracle 11g+）

原理：使用窗口函数 ROW_NUMBER () 生成连续序号
语法优势：无需嵌套多层子查询，可读性更高
示例：

SELECT *
FROM (SELECT e.*, ROW_NUMBER() OVER (ORDER BY employee_id) rnFROM employees e
)
WHERE rn BETWEEN 11 AND 20;

性能对比：

大数据量下（10 万 + 记录）性能优于 ROWNUM
需确保 ORDER BY 字段有索引以提升效率

三、FETCH FIRST/NEXT 子句（Oracle 12c + 新特性）

语法：

SELECT *
FROM table_name
ORDER BY column
FETCH FIRST n ROWS ONLY; -- 查询前n条  
SELECT *
FROM table_name
ORDER BY column
OFFSET m ROWS FETCH NEXT n ROWS ONLY; -- 跳过m条，取n条

示例（查询第 11-20 条）：

SELECT *
FROM employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

语法简洁，符合 ANSI SQL 标准
底层优化更好，大数据量分页性能最佳

四、分页方法对比表

方法	适用版本	语法复杂度	大数据性能	可读性
ROWNUM	所有版本	高（需多层嵌套）	一般	低
ROW_NUMBER()	11g+	中	较好	中
FETCH FIRST	12c+	低	最佳	高

五、分页性能优化建议

索引优化：对 ORDER BY 字段创建索引，避免全表扫描
** 避免 SELECT ***：只查询需要的字段，减少 IO 开销
大表分页方案：对于千万级数据，可使用以下优化写法：

SELECT *
FROM employees e
WHERE employee_id > (SELECT MAX(employee_id)FROM (SELECT employee_idFROM employeesORDER BY employee_idFETCH FIRST 10000 ROWS ONLY)
)
ORDER BY employee_id
FETCH FIRST 10 ROWS ONLY;

（通过主键范围查询减少扫描范围）

子查询与分页的结合应用

场景：查询各部门薪资前 3 的员工

SELECT d.department_name, e.employee_id, e.first_name, e.salary
FROM departments d
JOIN (SELECT department_id, employee_id, first_name, salary,ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) rnFROM employees
) e ON d.department_id = e.department_id
WHERE e.rn <= 3;

执行逻辑：