select * from emp ;
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
1 | 7369 | SMITH | CLERK | 7902 | 17-12月-80 | 800 | 20 | |
2 | 7499 | ALLEN | SALESMAN | 7698 | 20-2月 -81 | 1600 | 300 | 30 |
3 | 7521 | WARD | SALESMAN | 7698 | 22-2月 -81 | 1250 | 500 | 30 |
4 | 7566 | JONES | MANAGER | 7839 | 02-4月 -81 | 2975 | 20 | |
5 | 7654 | MARTIN | SALESMAN | 7698 | 28-9月 -81 | 1250 | 1400 | 30 |
6 | 7698 | BLAKE | MANAGER | 7839 | 01-5月 -81 | 2850 | 30 | |
7 | 7782 | CLARK | MANAGER | 7839 | 09-6月 -81 | 2450 | 10 | |
8 | 7788 | SCOTT | ANALYST | 7566 | 19-4月 -87 | 3000 | 20 | |
9 | 7839 | KING | PRESIDENT | 17-11月-81 | 5000 | 10 |
select * from dept ;
DEPTNO | DNAME | LOC | |
1 | 10 | ACCOUNTING | NEW YORK |
2 | 20 | RESEARCH | DALLAS |
3 | 30 | SALES | CHICAGO |
4 | 40 | OPERATIONS | BOSTON |
功能强大的WITH子句的用法
WITH
dept_costs AS (
SELECT d.dname, SUM(e.sal) AS dept_total
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname ),
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 dname ;
官方解释:
The WITH Clause Usage Notes
• It is used only with SELECT statements.
• A query name is visible to all WITH element query blocks (including their subquery blocks)
defined after it and the main query block itself (including its subquery blocks).
• When the query name is the same as an existing table name, the parser searches from the inside
out, the query block name takes precedence over the table name.
• The WITH clause can hold more than one query. Each query is then separated by a comma.