oracle with alias as (subsquery fatoring) 语句

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