DB2 公共表表达式（WITH语句的使用）--分析

最新推荐文章于 2021-06-08 09:15:41 发布

原创最新推荐文章于 2021-06-08 09:15:41 发布 · 594 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#db2 #bbs #oracle #数据库 #java #c

others 专栏收录该内容

18 篇文章

订阅专栏

本文介绍了DB2中WITH语句（公共表表达式）的使用，通过一个BBS表的例子展示了如何查询DB2的所有文章及评论。通过递归查询，解释了WITH语句的工作原理，并提醒了避免死循环的关键点。同时，讨论了去掉WHERE子句可能产生的死循环情况，并提供了反向验证的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

今天看了一篇文章，讲的是DB2中with的使用，顺便学习分析了下，原文重点摘录如下：http://blog.youkuaiyun.com/shangboerds/article/details/4601023

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

现在万事兼备了，我们开始查询吧。假设现在让你查询一下‘DB2 文章1’的所有评论，有人说，这还不简单，如下这样就可以了。

 SELECT * FROM BBS WHERE PARENTID=(SELECT ID FROM BBS WHERE NAME='DB2');  

答案完全正确。那么，现在让你查询一下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.PARENTID=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

最后请大家猜想一下，把语句1的where子句去掉，将会产生什么样的结果呢？

以上原文。

在文章最后提出把语句1的where子句去掉,会产生什么后果，应该会产生死循环。

原因是BBS表中第一条记录（0，0，’论坛首页‘）记录在他的父节点和子节点是一样的。

可以反向验证，将第一条记录去除，

WITH TEMP(PARENTID,ID,NA) AS
(
SELECT PARENTID,ID,NA FROM BBS WHERE na<>'论坛首页'---语句1
UNION ALL---语句2
SELECT B.PARENTID,B.ID,B.NA FROM BBS AS B, TEMP AS T WHERE B.PARENTID=T.ID---语句3
)
SELECT NA FROM TEMP

则可正常使用

数据库开发
DB2
DB2 文章1
DB2 文章1 的评论1
DB2 文章1 的评论2
DB2 文章2
Oracle
DB2
Oracle
DB2 文章1
DB2 文章2
DB2 文章1 的评论1
DB2 文章1 的评论2
DB2 文章1
DB2 文章2
DB2 文章1 的评论1
DB2 文章1 的评论2
DB2 文章1 的评论1
DB2 文章1 的评论2

验证上文提出with处理的步骤，可以增加语句5
WITH TEMP(PARENTID,ID,NA) AS
(
SELECT PARENTID,ID,NA FROM BBS WHERE na='DB2'---语句1
UNION ALL---语句2
SELECT B.PARENTID,B.ID,B.NA FROM BBS AS B, TEMP AS T WHERE B.PARENTID=T.ID---语句3
UNION ALL-
SELECT C.PARENTID,C.ID,C.NA FROM BBS AS C, TEMP AS T1 WHERE C.PARENTID=T1.ID---语句5

)
SELECT NA FROM TEMP

在with中加入了另一层循环，语句5

结果如下

DB2
DB2 文章1
DB2 文章2
DB2 文章1 的评论1
DB2 文章1 的评论2
DB2 文章1
DB2 文章2
DB2 文章1 的评论1
DB2 文章1 的评论2
DB2 文章1 的评论1
DB2 文章1 的评论2
DB2 文章1 的评论1
DB2 文章1 的评论2

可以看到，with将语句1与语句3的结果集合并起来，传给语句5，至于最后

’DB2 文章1 的评论1

DB2 文章1 的评论2‘

1.11 衍生出两次 111.1111 ，111.1112

11.111 衍生出一次 111.1111 111.1112

则证明了，上文with处理步骤是正确的.