DB2公共表表达式WITH语句使用文档格式.docx
《DB2公共表表达式WITH语句使用文档格式.docx》由会员分享，可在线阅读，更多相关《DB2公共表表达式WITH语句使用文档格式.docx（10页珍藏版）》请在冰豆网上搜索。
5.）;
1.WITH
TEST（NAME_TEST,
BDAY_TEST）
AS
2.（
3.SELECT
NAME,BIRTHDAY
FROM
USER--语句1
4.）
5.SELECT
NAME_TEST
TEST
WHERE
BDAY_TEST='
1949-10-1'
;
--语句2
下面我们来解释一下，首先语句1执行，它会产生一个有两列（NAME,BIRTHDAY）的结果集；
接着，我们将这个结果集命名为test，并且将列名重命名为NAME_TEST,BDAY_TEST；
最后我们执行语句2，从这个临时集合中找到生日是1949-10-1，也就是共和国的同龄人。
怎么样？
如果你感觉不好理解，请仔细的分析一下上面的语句。
下面我们举个VALUES语句和WITH语句结合使用的例子，如下：
3.VALUES
（'
张三'
'
1997-7-1'
）,（'
李四'
）
从上面的介绍和WITH语句不为大多数人所熟悉可以猜测，WITH语句是为复杂的查询为设计的，的确是这样的，下面我们举个复杂的例子，想提高技术的朋友可千万不能错过。
考虑下面的情况：
3.NAME
NULL,--姓名
4.DEGREE
INTEGER
NULL,--学历（1、专科
2、本科
3、硕士
4、博士）
5.STARTWORKDATE
date
NULL,--入职时间
6.SALARY1
FLOAT
NULL,--基本工资
7.SALARY2
NULL--奖金
8.）;
假设现在让你查询一下那些1、学历是硕士或博士
2、学历相同，入职年份也相同，但是工资（基本工资+奖金）却比相同条件员工的平均工资低的员工。
（哈哈，可能是要涨工资），不知道你听明白问题没有？
该怎么查询呢？
我们是这样想的：
1、查询学历是硕士或博士的那些员工得到结果集1，如下：
1.SELECT
NAME,DEGREE,YEAR（STARTWORKDATE）
WORDDATE,
SALARY1+SALARY2
SALARY
DEGREE
IN
（3,4）；
2、根据学历和入职年份分组，求平均工资得到结果集2，如下：
DEGREE,YEAR（STARTWORKDATE）
AVG（SALARY1+SALARY2）
AVG_SALARY
2.FROM
（3,4）
3.GROUP
BY
3、以学历和入职年份为条件
联合两个结果集，查找工资<
平均工资
的员工，以下是完整的SQL：
TEMP1（NAME,DEGREE,WORDDATE,SALARY）
4.）,
5.TEMP2
（DEGREE,WORDDATE,AVG_SALARY）
6.（
7.SELECT
8.FROM
9.GROUP
10.）
11.SELECT
NAME
TEMP1,
TEMP2
12.=
13.AND
=
14.AND
SALARY<
AVG_SALARY;
查询结果完全正确，但我们还有改善的空间，在查询结果集2的时候，我们是从user表中取得数据的。
其实此时结果集1已经查询出来了，我们完全可以从结果集1中通过分组得到结果集2，而不用从uer表中得到结果集2，比较上面和下面的语句你就可以知道我说的是什么意思了！
DEGREE,WORDDATE,
AVG（SALARY）
TEMP1
DEGREE,WORDDATE
可能有些朋友会说，我不用WITH语句也可以查出来，的确是这样，如下：
U,
4.FROM
5.GROUP
6.）
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语句做递归查询。
递归查询的一个典型的例子是对树状结构的表进行查询，考虑如下的情况：
1.论坛首页
2.--数据库开发
3.----DB2
4.------DB2
文章1
5.--------DB2
的评论1
6.--------DB2
的评论2
7.------DB2
文章2
8.----Oracle
9.--Java技术
以上是一个论坛的典型例子，下面我们新建一个表来存储以上信息。
BBS
3.PARENTID
NULL,
4.ID
5.NAME
VARCHAR（200）
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,'
的评论1'
13.（111,1112,'
的评论2'
14.（11,112,'
文章2'
15.（1,12,'
Oracle'
16.（0,2,'
Java技术'
）;
现在万事兼备了，我们开始查询吧。
假设现在让你查询一下‘DB2文章1’的所有评论，有人说，这还不简单，如下这样就可以了。
*
PARENTID=（SELECT
ID
NAME='
答案完全正确。
那么，现在让你查询一下DB2的所有文章及评论，怎么办？
传统的方法就很难查询了，这时候递归查询就派上用场了，如下：
TEMP（PARENTID,ID,NAME）
PARENTID,ID,NAME
---语句1
4.UNION
ALL---语句2
,
B,
TEMP
T
=语句3
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=
--结果：
王五
北京
李四
杭州#广州
张三
杭州#郑州#南昌
升级会员 