DB2公共表表达式WITH语句使用.docx

DB2公共表表达式WITH语句使用.docx

《DB2公共表表达式WITH语句使用.docx》由会员分享，可在线阅读，更多相关《DB2公共表表达式WITH语句使用.docx（10页珍藏版）》请在冰豆网上搜索。

DB2公共表表达式WITH语句使用

WITH语句

----start

   说起WITH语句，除了那些第一次听说WITH语句的人，大部分人都觉得它是用来做递归查询的。

其实那只是它的一个用途而已，它的本名正如我们标题写的那样，叫做：

公共表表达式（CommonTableExpression），从字面理解，大家觉得它是用来干嘛的呢？

其实，它是用来定义临时集合的。

啊？

VALUES语句不是用来定义临时集合的吗？

怎么WITH语句也用来定义临时集合呢？

它们有什么区别呢？

VALUES语句是用明确的值来定义临时集合的，如下：

[c-sharp] 

1.VALUES （1,2）, （1,3）,（2,1）  

WITH语句是用查询（也就是select语句）来定义临时集合的，从这个角度讲，有点像视图，不过不是视图，大家千万别误解。

如下：

[c-sharp]

1.CREATE TABLE USER （  

2.NAME VARCHAR（20） NOT NULL,---姓名  

3.SEX INTEGER,---性别（1、男   2、女）  

4.BIRTHDAY DATE---生日  

5.）;  

[c-sharp] 

1.WITH TEST（NAME_TEST, BDAY_TEST） AS  

2.（  

3.SELECT NAME,BIRTHDAY FROM USER--语句1  

4.）  

5.SELECT NAME_TEST FROM TEST WHERE BDAY_TEST='1949-10-1';--语句2  

下面我们来解释一下，首先语句1执行，它会产生一个有两列（NAME,BIRTHDAY）的结果集；接着，我们将这个结果集命名为test，并且将列名重命名为NAME_TEST,BDAY_TEST；最后我们执行语句2，从这个临时集合中找到生日是1949-10-1，也就是共和国的同龄人。

怎么样？

如果你感觉不好理解，请仔细的分析一下上面的语句。

下面我们举个VALUES语句和WITH语句结合使用的例子，如下：

[c-sharp]

1.WITH TEST（NAME_TEST, BDAY_TEST） AS  

2.（  

3.VALUES （'张三','1997-7-1'）,（'李四','1949-10-1'）  

4.）  

5.SELECT NAME_TEST FROM TEST WHERE BDAY_TEST='1949-10-1'  

从上面的介绍和WITH语句不为大多数人所熟悉可以猜测，WITH语句是为复杂的查询为设计的，的确是这样的，下面我们举个复杂的例子，想提高技术的朋友可千万不能错过。

考虑下面的情况：

[c-sharp] 

1.CREATE TABLE USER  

2.（  

3.NAME VARCHAR（20） NOT NULL,--姓名  

4.DEGREE INTEGER NOT NULL,--学历（1、专科 2、本科 3、硕士 4、博士）  

5.STARTWORKDATE date NOT NULL,--入职时间  

6.SALARY1 FLOAT NOT NULL,--基本工资  

7.SALARY2 FLOAT NOT NULL--奖金  

8.）;  

假设现在让你查询一下那些1、学历是硕士或博士 2、学历相同，入职年份也相同，但是工资（基本工资+奖金）却比相同条件员工的平均工资低的员工。

（哈哈，可能是要涨工资），不知道你听明白问题没有？

该怎么查询呢？

我们是这样想的：

1、查询学历是硕士或博士的那些员工得到结果集1，如下：

[c-sharp] 

1.SELECT NAME,DEGREE,YEAR（STARTWORKDATE） AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN （3,4）；  

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

[c-sharp] 

1.SELECT DEGREE,YEAR（STARTWORKDATE） AS WORDDATE, AVG（SALARY1+SALARY2） AS AVG_SALARY   

2.FROM USER WHERE DEGREE IN （3,4）  

3.GROUP BY DEGREE,YEAR（STARTWORKDATE）  

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

[c-sharp] 

1.WITH TEMP1（NAME,DEGREE,WORDDATE,SALARY） AS   

2.（  

3.SELECT NAME,DEGREE,YEAR（STARTWORKDATE） AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN （3,4）  

4.）,  

5.TEMP2 （DEGREE,WORDDATE,AVG_SALARY） AS  

6.（  

7.SELECT DEGREE,YEAR（STARTWORKDATE） AS WORDDATE, AVG（SALARY1+SALARY2） AS AVG_SALARY   

8.FROM USER WHERE DEGREE IN （3,4）  

9.GROUP BY DEGREE,YEAR（STARTWORKDATE）  

10.）  

11.SELECT NAME FROM TEMP1, TEMP2 WHERE   

12.=   

13.AND =   

14.AND SALARY

查询结果完全正确，但我们还有改善的空间，在查询结果集2的时候，我们是从user表中取得数据的。

其实此时结果集1已经查询出来了，我们完全可以从结果集1中通过分组得到结果集2，而不用从uer表中得到结果集2，比较上面和下面的语句你就可以知道我说的是什么意思了！

[c-sharp] 

1.WITH TEMP1（NAME,DEGREE,WORDDATE,SALARY） AS   

2.（  

3.SELECT NAME,DEGREE,YEAR（STARTWORKDATE） AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN （3,4）  

4.）,  

5.TEMP2 （DEGREE,WORDDATE,AVG_SALARY） AS  

6.（  

7.SELECT DEGREE,WORDDATE, AVG（SALARY） AS AVG_SALARY   

8.FROM TEMP1  

9.GROUP BY DEGREE,WORDDATE  

10.）  

11.SELECT NAME FROM TEMP1, TEMP2 WHERE   

12.=   

13.AND =   

14.AND SALARY

可能有些朋友会说，我不用WITH语句也可以查出来，的确是这样，如下：

[c-sharp]

1.SELECT  FROM USER AS U,  

2.（  

3.SELECT DEGREE,YEAR（STARTWORKDATE） AS WORDDATE, AVG（SALARY1+SALARY2） AS AVG_SALARY   

4.FROM USER WHERE DEGREE IN （3,4）  

5.GROUP BY DEGREE,YEAR（STARTWORKDATE）  

6.） AS G  

7.WHERE =  

8.AND YEAR=  

9.AND （SALARY1+SALARY2）<;  

那使用WITH和不使用WITH，这两种写法有什么区别呢？

一般情况下这两种写法在性能上不会有太大差异，但是，

1、当USER表的记录很多

2、硕士或博士（DEGREEIN（3,4））在USER表中的比例很少

当满足以上条件时，这两种写法在性能的差异将会显现出来，为什么呢？

因为不使用WITH写法的语句访问了2次USER表，如果DEGREE字段又没有索引，性能差异将会非常明显。

   当你看到这时，如果很好的理解了上面的内容，我相信你会对WITH语句有了一定的体会。

然而WITH语句能做的还不止这些，下面给大家介绍一下，如何用WITH语句做递归查询。

递归查询的一个典型的例子是对树状结构的表进行查询，考虑如下的情况：

[c-sharp] 

1.论坛首页  

2.--数据库开发  

3.----DB2  

4.------DB2 文章1  

5.--------DB2 文章1 的评论1  

6.--------DB2 文章1 的评论2  

7.------DB2 文章2  

8.----Oracle  

9.--Java技术  

以上是一个论坛的典型例子，下面我们新建一个表来存储以上信息。

[c-sharp] 

1.CREATE TABLE BBS  

2.（  

3.PARENTID INTEGER NOT NULL,  

4.ID INTEGER NOT NULL,  

5.NAME VARCHAR（200） NOT NULL---板块、文章、评论等。

6.）;  

7.insert into bbs （PARENTID,ID,NAME） values   

8.（0,0,'论坛首页'）,  

9.（0,1,'数据库开发'）,  

10.（1,11,'DB2'）,  

11.（11,111,'DB2 文章1'）,  

12.（111,1111,'DB2 文章1 的评论1'）,  

13.（111,1112,'DB2 文章1 的评论2'）,  

14.（11,112,'DB2 文章2'）,  

15.（1,12,'Oracle'）,  

16.（0,2,'Java技术'）;  

现在万事兼备了，我们开始查询吧。

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

[c-sharp] 

1.SELECT * FROM BBS WHERE PARENTID=（SELECT ID FROM BBS WHERE NAME='DB2'）;  

答案完全正确。

那么，现在让你查询一下DB2的所有文章及评论，怎么办？

传统的方法就很难查询了，这时候递归查询就派上用场了，如下：

[c-sharp] 

1.WITH TEMP（PARENTID,ID,NAME） AS  

2.（  

3.SELECT PARENTID,ID,NAME FROM BBS WHERE NAME='DB2'---语句1  

4.UNION ALL---语句2  

5.SELECT ,, FROM BBS AS B, TEMP AS T WHERE =语句3  

6.）  

7.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必须是UNIONALL

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

DB2递归实现字符串分组连接操作

db2中的递归查询使用with来实现，也称为公共表达式，公共表达式在select语句的开始部分采用with子句的形式，在使用公共表达式的查询中可以多次使用它，并且公共表达式还可以通过取别名来连接到他本身，这样的话就可以达到循环的目的。

递归查询通常有3个部分需要定义：

一：

一个公共表达式形式的虚拟表。

二：

一个初始化表。

三：

一个与虚拟表进行完全内连接的辅助表。

需要使用UNIONall合并上边3个查询，然后用select从递归输出中得到最终的结果。

大体上如下形式

withXX（x1,x2,x3）as -------@0

（

select,froma ----@1

unionall ----@2

select*froma,xxwhere=------@3

）

select...fromxxwhere....-------@4

@0:

为with体，即虚拟表

@1:

为初始化表，这里需要定义初始化的一些行，也就是你递归的出发点，或者说父行，这部分逻辑只执行一次，它的结果作为虚拟表递归的初始化内容。

@2:

这里必须用UNIONall

@3:

这里需要定义递归的条件（辅助表），这里定义递归的逻辑，需要注意的是父行和子行进行连接的时候逻辑一定要清楚父子关系，不然很容易变成死循环的，这里首先将初始化表的结果作为条件进行查询，在把执行的结果添加到虚拟表中，只要这里能查询出来记录，那么就会进行下一步递归循环。

@4:

这里就是对虚拟表的查询语句。

需求：

一张实时表，一个人每到一个地方，就有一条记录存在，存放样例：

张三上海

张三杭州

.....

要求，按人名进行汇总，将他到过的地方拼接，中间用'#'分隔

数据准备：

--创建表：

CREATETABLERecursive_Test

（User_NameVARCHAR（12）,

 CityVARCHAR（12））;

--数据插入

INSERTINTORecursive_Test（User_Name,City）

   values（'张三','杭州'）,（'张三','郑州'）,（'李四','杭州'）,

       （'张三','南昌'）,（'李四','广州'）,（'王五','北京'）;

-----递归实现：

WITHRecursive_Test_Par（User_Name,City,Rk_Num）as（

SELECTUser_Name,City,ROW_NUMBER（）OVER（PARTITIONBYUser_Name）--分组，生成序列，自我关联之用

FROMRecursive_Test

）,

City_Join（User_Name,City,R_Num）as（

SELECTUser_Name,CAST（CityASVARCHAR（100））,Rk_NumfromRecursive_Test_ParWHERERk_Num=1

UNIONALL

SELECT,CAST||'#'||ASVARCHAR（100））,+1

 fromCity_Joina1,Recursive_Test_Parb1

WHERE=and=

） 

SELECT,FROMCity_JoinaINNERJOIN

   （SELECTUser_Name,max（R_Num）R_NumfromCity_Join

    GROUPBYUser_Name）b

ON=and=;

（另：

WITHRECURSIVE_TEST_PAR（USER_NAME,CITY,RK_NUM）AS（

SELECTUSER_NAME,CITY,ROW_NUMBER（）OVER（PARTITIONBYUSER_NAME）

FROMRECURSIVE_TEST）

CITY_JOIN（USER_NAME,CITY,R_NUM）AS（

SELECTUSER_NAME,CAST（CITYASVARCHAR（100））,RK_NUM

FROMRECURSIVE_TEST_PARWHERERK_NUM=1

UNIONALL

SELECT,CASTASVARCHAR（100）||'#'||,

FROMRECURSIVE_TEST_PARA,CITY_JOINB

WHERE=AND=

）

SELECT,A.CITYFROMCITY_JOINAINNERJOIN

（SELECTUSER_NAME,MAX（R_NUM）R_NUMFROMCITY_JOIN

GROUPBYUSER_NAME）B

ON=AND=

 --结果：

王五

北京

李四

杭州#广州

张三

杭州#郑州#南昌