oracle

使用子查询因式，可以给子查询命名，在后续的查询中可以多处使用这个子查询。
WITH
dept_costs AS (
SELECT department_name, SUM(salary) dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >
(SELECT avg FROM avg_cost)
ORDER BY department_name;

DEPARTMENT_NAME DEPT_TOTAL
------------------------------ ----------
Sales 313800
Shipping 156400


等级查询
递归地查询一个表。
SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
LEVEL返回嵌套的等级，根节点是1,依次递增。


SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;
SIBLINGS 关键字使的排序不打乱上级的排列。