例：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id
SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);
⑥EXISTS 操作符
EXISTS 操作符检查在子查询中是否存在满足条件的行
1.如果在子查询中存在满足条件的行:
不在子查询中继续查找
条件返回 TRUE
2.如果在子查询中不存在满足条件的行:
条件返回 FALSE
继续在子查询中查找
例：查询公司管理者的employee_id,last_name,job_id,department_id信息
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT 'X'
FROM employees
WHERE manager_id =
outer.employee_id);
例：查询departments表中，不存在于employees表中的部门的department_id和department_name
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id
= d.department_id);
⑦相关更新
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column =
alias2.column);
使用相关子查询依据一个表中的数据更新另一个表的数据
⑧相关删除
DELETE FROM table1 alias1
WHERE column operator
(SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
⑨WITH 子句
使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块
WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中
使用 WITH 子句可以提高查询效率
例：查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
WITH dept_costs AS (
SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total >
(SELECT dept_avg
FROM avg_cost)
ORDER BY department_name;
Oracle数据库高级查询