Oracle版 WITH语句的使用

  本文是看了DB2 SQL精粹有感写的Oracle版本，毕竟两个大公司的产品有不同嘛。
  说起WITH 语句，除了那些第一次听说WITH 语句的人，大部分人都觉得它是用来做
递归查询的。其实那只是它的一个用途而已，它的本名正如我们标题写的那样，叫做：公共
表表达式（Common Table Expression），从字面理解，大家觉得它是用来干嘛的呢？
其实，它是用来定义临时集合的。

WITH TEMP(ID,USER) AS(
SELECT ‘01’,’SAM’ FROM DUAL
UNION ALL
SELECT ‘02’,’MIKE’ FROM DUAL
UNION ALL
SELECT ‘03’,’TOM’ FROM DUAL
UNION ALL
SELECT ‘04’,’JANE’ FROM DUAL
)
SELECT * FROM TEMP;

WITH可以定义多个结果集，中间用逗号分隔。
(这种写法更加符合普通思维的逻辑，写出来的代码更加容易理解)
WITH 语句是为复杂的查询为设计的，的确是这样的，下面我们举个复杂的例子，想提高技术的朋友可千万不能错过。考虑下面的情况：

CREATE TABLE USER
(
 NAME VARCHAR(20) NOT NULL,--姓名
 DEGREE INTEGER NOT NULL,--学历(1、专科2、本科3、硕士4、博士)
 STARTWORKDATE date NOT NULL,--入职时间
 SALARY1 FLOAT NOT NULL,--基本工资
 SALARY2 FLOAT NOT NULL--奖金
);

假设现在让你查询一下那些1、学历是硕士或博士2、学历相同，入职年份也相同，但是工资（基本工资+奖金）却比相同条件员工的平均工资低的员工。（哈哈，可能是要涨工资），不知道你听明白问题没有？该怎么查询呢？我们是这样想的：
1、查询学历是硕士或博士的那些员工得到结果集1，如下：

 SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN (3,4)；

2、根据学历和入职年份分组，求平均工资得到结果集2，如下：

 SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY
 FROM USER WHERE DEGREE IN (3,4)
 GROUP BY DEGREE,YEAR(STARTWORKDATE)

3、以学历和入职年份为条件联合两个结果集，查找工资<平均工资的员工，以下是完整的SQL：

 WITH TEMP1(NAME,DEGREE,WORDDATE,SALARY) AS
 (
 SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN (3,4)
 ),
 TEMP2 (DEGREE,WORDDATE,AVG_SALARY) AS
 (
 SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY
 FROM USER WHERE DEGREE IN (3,4)
 GROUP BY DEGREE,YEAR(STARTWORKDATE)
 )
 SELECT NAME FROM TEMP1, TEMP2 WHERE
 TEMP1.DEGREE=TEMP2.DEGREE
 AND TEMP1.WORDDATE=TEMP2.WORDDATE
 AND SALARY<AVG_SALARY;

查询结果完全正确，但我们还有改善的空间，在查询结果集2 的时候，我们是从user 表中取得数据的。其实此时结果集1 已经查询出来了，我们完全可以从结果集1 中通过分组得到结果集2，而不用从uer 表中得到结果集2，比较上面和下面的语句你就可以知道我说的是什么意思了！

WITH TEMP1(NAME,DEGREE,WORDDATE,SALARY) AS
(
SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN (3,4)
 ),
 TEMP2 (DEGREE,WORDDATE,AVG_SALARY) AS
 (
 SELECT DEGREE,WORDDATE, AVG(SALARY) AS AVG_SALARY
 FROM TEMP1
 GROUP BY DEGREE,WORDDATE
 )
 SELECT NAME FROM TEMP1, TEMP2 WHERE
 TEMP1.DEGREE=TEMP2.DEGREE
 AND TEMP1.WORDDATE=TEMP2.WORDDATE
 AND SALARY<AVG_SALARY;

当你看到这时，如果很好的理解了上面的内容，我相信你会对WITH 语句有了一定的体会。然而WITH 语句能做的还不止这些，下面给大家介绍一下，如何用WITH 语句做递归查询。

 CREATE TABLE BBS
 (
 PARENTID INTEGER NOT NULL,
 ID INTEGER NOT NULL,
 NAME VARCHAR(200) NOT NULL
 );
insert into bbs (PARENTID,ID,NAME) values (0,1,'数据库开发');
insert into bbs (PARENTID,ID,NAME) values (1,11,'DB2');
insert into bbs (PARENTID,ID,NAME) values (11,111,'DB2 文章1');
insert into bbs (PARENTID,ID,NAME) values (111,1111,'DB2 文章1 的评论1');
insert into bbs (PARENTID,ID,NAME) values (111,1112,'DB2 文章1 的评论2');
insert into bbs (PARENTID,ID,NAME) values (11,112,'DB2 文章2');
insert into bbs (PARENTID,ID,NAME) values (1,12,'Oracle');
insert into bbs (PARENTID,ID,NAME) values (0,2,'Java 技术');
COMMIT;

那么，现在让你查询一下DB2 的所有文章及评论，怎么办？传统的方法就
很难查询了，这时候递归查询就派上用场了，如下：

WITH TEMP(PARENTID,ID,NAME) AS
 (
 SELECT PARENTID,ID,NAME FROM BBS WHERE NAME='DB2'---语句1
 UNION ALL---语句2
 SELECT B.PARENTID,B.ID,B.NAME FROM BBS AS B, TEMP AS T WHERE B.PARENTI
D=T.ID---语句3
 )
 SELECT NAME FROM TEMP;---语句4

运行后，我们发现，结果完全正确，那它到底是怎么运行的呢？下面我们详细讲解一下。
1、首先，语句1 将会执行，它只执行一次，作为循环的起点。得到结果集：DB2
2、接着，将循环执行语句3，这里我们有必要详细介绍一下。
首先语句3 的意图是什么呢？说白了，它就是查找语句1产生结果集（DB2）的下一级，那么在目录树中DB2 的下一级是什么呢？是‘DB2 文章1’和‘DB2 文章2’，并且把查询到的结果集作为下一次循环的起点，然后查询它们的下一级，直到没有下一级为止。
怎么样？还没明白？哈哈，不要紧，我们一步一步来：
首先，语句1 产生结果集：DB2，作为循环的起点，把它和BBS 表关联来查找它的下一级，查询后的结果为：‘DB2 文章1’和‘DB2 文章2’接着，把上次的查询结果（也就是‘DB2 文章1’和‘DB2 文章2’）和BBS 表关联来查找它
们的下一级，查询后的结果为：‘DB2 文章1 的评论1’ 和‘DB2 文章1 的评论2’。然后，在把上次的查询结果（也就是‘DB2 文章1 的评论1’ 和‘DB2 文章1 的评论2’）和BBS 表关联来查找它们的下一级，此时，没有结果返回，循环结束。
3、第三，将执行语句2，将所有的结果集放在一起，最终得到temp 结果集。
4、最后，我们通过语句4 从temp 临时集合中得到我们期望的查询结果。怎么样，这回理解了吧，如果还没有理解，那么我也无能为力了。
需要特别提醒的是
1、一定要注意语句3 的关联条件，否则很容易就写成死循环了。
2、语句2必须是UNION ALL

接下来，如果做如此更改后，再次执行WITH 递归语句

UPDATE BBS SET ID = 1 WHERE PARENTID = 1;

再次执行则会出现以下错误：
ERROR:ORA-32044:执行递归WITH查询时检测到循环

这时候，可以使用CYCLE语句忽略循环导致的错误，如下：

WITH TEMP(PARENTID,ID,NAME) AS
 (
 SELECT PARENTID,ID,NAME FROM BBS WHERE NAME='DB2'---语句1
 UNION ALL---语句2
 SELECT B.PARENTID,B.ID,B.NAME FROM BBS AS B, TEMP AS T WHERE B.PARENTI
D=T.ID---语句3
 )
 CYCLE ID SET DUP_IND TO 'Y' DEFAULT 'N'
 SELECT NAME FROM TEMP;---语句4

此时我们得到多了一列出来，DUP_IND
（PS.需要注意的是，这个结果和使用CONNECT BY NOCYCLE得到的结果并不一致，ID为1的记录会出现两次。后面会降到CONNECT BY语句的使用）

DUP_IND为’Y’的时候，表示出现了死循环。

同时CYCLE可以判断在在同一叶子上是否出现重复的值。
如果执行：

insert into bbs (PARENTID,ID,NAME) values (11,112,'DB2 文章1');

再次执行带CYCLE的递归查询语句。
也会发现有一个’Y’在上面，因为后面的“DB2 文章1”内容重复了。

但是，如果执行的插入数据语句是：

insert into bbs (PARENTID,ID,NAME) values (111,1113,'DB2 文章1');

则不会出现Y。