1、使用语法
With
table_name1[column_name1,...,column_nameN] As (subquery 1),
table_name2[column_name1,...,column_nameN] As (subquery 2),
......
table_nameN[column_name1,...,column_nameN] As (subqueryN),
Select column_name1,...,column_nameN From table_name1,...,table_nameN Where....
2、实例
例1:
SQL> With
2 部门表(部门号,部门名称,位置) As (Select deptno,dname,loc From scott.dept Where deptno=10),
3 员工表(工号,姓名,部门号) As (Select empno,ename,deptno From scott.emp)
4 Select * From 部门表 a,员工表 b Where a.部门号(+)=b.部门号
5 /
部门号 部门名称 位置 工号 姓名 部门号
------ -------------- ------------- ----- ---------- ------
10 ACCOUNTING NEW YORK 7934 MILLER 10
10 ACCOUNTING NEW YORK 7839 KING 10
10 ACCOUNTING NEW YORK 7782 CLARK 10
7900 JAMES 30
7844 TURNER 30
7698 BLAKE 30
7654 MARTIN 30
7521 WARD 30
7499 ALLEN 30
7902 FORD 20
7876 ADAMS 20
7788 SCOTT 20
7566 JONES 20
7369 SMITH 20
14 rows selected
SQL>
例2:
SQL> With
2 部门 As (Select deptno As 部门号,dname As 部门名 From dept),
3 雇员 As (Select empno As 雇员号,ename As 雇员名,sal As 工资,deptno As 部门号 From emp)
4 Select a.部门号,a.部门名,b.雇员号,b.雇员名 ,c.grade As 工资等级
5 From 部门 a,雇员 b,salgrade c
6 Where a.部门号=b.部门号 And b.工资 Between c.losal And c.hisal
7 Order By a.部门号,c.grade
8 /
部门号 部门名 雇员号 雇员名 工资等级
------ -------------- ------ ---------- ----------
10 ACCOUNTING 7934 MILLER 2
10 ACCOUNTING 7782 CLARK 4
10 ACCOUNTING 7839 KING 5
20 RESEARCH 7369 SMITH 1
20 RESEARCH 7876 ADAMS 1
20 RESEARCH 7902 FORD 4
20 RESEARCH 7788 SCOTT 4
20 RESEARCH 7566 JONES 4
30 SALES 7900 JAMES 1
30 SALES 7654 MARTIN 2
30 SALES 7521 WARD 2
30 SALES 7844 TURNER 3
30 SALES 7499 ALLEN 3
30 SALES 7698 BLAKE 4
14 rows selected
例3:
SQL> With
2 部门 As (Select deptno As 部门号,dname As 部门名 From dept),
3 部门2 As (Select deptno As 部门号,dname As 部门名 From dept Where deptno In (10,20))
4 Select * From 部门
5 Minus
6 Select * From 部门2
7 /
部门号 部门名
------ --------------
30 SALES
40 OPERATIONS
例3:
SQL> Create Table t(部门号 Number,部门名 varchar2(20),雇员号 Number,雇员名 Varchar2(20),工资等级 Number);
Table created
SQL> Insert Into t
2 With
3 部门 As (Select deptno As 部门号,dname As 部门名 From dept),
4 雇员 As (Select empno As 雇员号,ename As 雇员名,sal As 工资,deptno As 部门号 From emp)
5 Select a.部门号,a.部门名,b.雇员号,b.雇员名 ,c.grade As 工资等级
6 From 部门 a,雇员 b,salgrade c
7 Where a.部门号=b.部门号 And b.工资 Between c.losal And c.hisal
8 Order By a.部门号,c.grade
SQL> /
14 rows inserted
SQL> Select * From t;
部门号 部门名 雇员号 雇员名 工资等级
---------- -------------------- ---------- -------------------- ----------
10 ACCOUNTING 7934 MILLER 2
10 ACCOUNTING 7782 CLARK 4
10 ACCOUNTING 7839 KING 5
20 RESEARCH 7369 SMITH 1
20 RESEARCH 7876 ADAMS 1
20 RESEARCH 7902 FORD 4
20 RESEARCH 7788 SCOTT 4
20 RESEARCH 7566 JONES 4
30 SALES 7900 JAMES 1
30 SALES 7654 MARTIN 2
30 SALES 7521 WARD 2
30 SALES 7844 TURNER 3
30 SALES 7499 ALLEN 3
30 SALES 7698 BLAKE 4
14 rows selected