oracle with alias as (subsquery fatoring) 语句

最新推荐文章于 2024-09-15 19:41:08 发布

原创最新推荐文章于 2024-09-15 19:41:08 发布 · 312 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

50 篇文章

订阅专栏

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