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

1、DB2公共表表达式WITH语句使用WITH语句-start 说起WITH 语句，除了那些第一次听说WITH语句的人，大部分人都觉得它是用来做递归查询的。其实那只是它的一个用途而已，它的本名正如我们标题写的那样，叫做：公共表表达式（Common Table Expression），从字面理解，大家觉得它是用来干嘛的呢？其实，它是用来定义临时集合的。啊？VALUES语句不是用来定义临时集合的吗？怎么WITH语句也用来定义临时集合呢？它们有什么区别呢？VALUES语句是用明确的值来定义临时集合的，如下：c-sharp1. VALUES(1,2),(1,3),(2,1)WITH语句是用查询(也就是se

2、lect语句)来定义临时集合的，从这个角度讲，有点像视图，不过不是视图，大家千万别误解。如下：c-sharp 1. CREATETABLEUSER(2. NAMEVARCHAR(20)NOTNULL,-姓名3. SEXINTEGER,-性别（1、男2、女）4. BIRTHDAYDATE-生日5. );c-sharp1. WITHTEST(NAME_TEST,BDAY_TEST)AS2. (3. SELECTNAME,BIRTHDAYFROMUSER-语句14. )5. SELECTNAME_TESTFROMTESTWHEREBDAY_TEST=1949-10-1;-语句2下面我们来解释一下，首

3、先语句1执行，它会产生一个有两列（NAME,BIRTHDAY）的结果集；接着，我们将这个结果集命名为test，并且将列名重命名为NAME_TEST, BDAY_TEST；最后我们执行语句2，从这个临时集合中找到生日是1949-10-1，也就是共和国的同龄人。怎么样？如果你感觉不好理解，请仔细的分析一下上面的语句。下面我们举个VALUES语句和WITH语句结合使用的例子，如下：c-sharp 1. WITHTEST(NAME_TEST,BDAY_TEST)AS2. (3. VALUES(张三,1997-7-1),(李四,1949-10-1)4. )5. SELECTNAME_TESTFROMTE

4、STWHEREBDAY_TEST=1949-10-1从上面的介绍和WITH语句不为大多数人所熟悉可以猜测，WITH语句是为复杂的查询为设计的，的确是这样的，下面我们举个复杂的例子，想提高技术的朋友可千万不能错过。考虑下面的情况：c-sharp1. CREATETABLEUSER2. (3. NAMEVARCHAR(20)NOTNULL,-姓名4. DEGREEINTEGERNOTNULL,-学历(1、专科2、本科3、硕士4、博士)5. STARTWORKDATEdateNOTNULL,-入职时间6. SALARY1FLOATNOTNULL,-基本工资7. SALARY2FLOATNOTNULL

5、-奖金8. );假设现在让你查询一下那些 1、学历是硕士或博士 2、学历相同，入职年份也相同，但是工资（基本工资+奖金）却比相同条件员工的平均工资低的员工。（哈哈，可能是要涨工资），不知道你听明白问题没有？该怎么查询呢？我们是这样想的：1、查询学历是硕士或博士的那些员工得到结果集1，如下：c-sharp1. SELECTNAME,DEGREE,YEAR(STARTWORKDATE)ASWORDDATE,SALARY1+SALARY2ASSALARYFROMUSERWHEREDEGREEIN(3,4)；2、根据学历和入职年份分组，求平均工资 得到结果集2，如下：c-sharp1. SELECTD

6、EGREE,YEAR(STARTWORKDATE)ASWORDDATE,AVG(SALARY1+SALARY2)ASAVG_SALARY2. FROMUSERWHEREDEGREEIN(3,4)3. GROUPBYDEGREE,YEAR(STARTWORKDATE)3、以学历和入职年份为条件联合两个结果集，查找工资平均工资的员工，以下是完整的SQL：c-sharp1. WITHTEMP1(NAME,DEGREE,WORDDATE,SALARY)AS2. (3. SELECTNAME,DEGREE,YEAR(STARTWORKDATE)ASWORDDATE,SALARY1+SALARY2ASSA

7、LARYFROMUSERWHEREDEGREEIN(3,4)4. ),5. TEMP2(DEGREE,WORDDATE,AVG_SALARY)AS6. (7. SELECTDEGREE,YEAR(STARTWORKDATE)ASWORDDATE,AVG(SALARY1+SALARY2)ASAVG_SALARY8. FROMUSERWHEREDEGREEIN(3,4)9. GROUPBYDEGREE,YEAR(STARTWORKDATE)10. )11. SELECTNAMEFROMTEMP1,TEMP2WHERE12. =13. AND=14. ANDSALARYAVG_SALARY;查询结果

8、完全正确，但我们还有改善的空间，在查询结果集2的时候，我们是从user表中取得数据的。其实此时结果集1已经查询出来了，我们完全可以从结果集1中通过分组得到结果集2，而不用从uer表中得到结果集2，比较上面和下面的语句你就可以知道我说的是什么意思了！c-sharp1. WITHTEMP1(NAME,DEGREE,WORDDATE,SALARY)AS2. (3. SELECTNAME,DEGREE,YEAR(STARTWORKDATE)ASWORDDATE,SALARY1+SALARY2ASSALARYFROMUSERWHEREDEGREEIN(3,4)4. ),5. TEMP2(DEGREE,W

9、ORDDATE,AVG_SALARY)AS6. (7. SELECTDEGREE,WORDDATE,AVG(SALARY)ASAVG_SALARY8. FROMTEMP19. GROUPBYDEGREE,WORDDATE10. )11. SELECTNAMEFROMTEMP1,TEMP2WHERE12. =13. AND=14. ANDSALARYAVG_SALARY;可能有些朋友会说，我不用WITH语句也可以查出来，的确是这样，如下：c-sharp 1. SELECTFROMUSERASU,2. (3. SELECTDEGREE,YEAR(STARTWORKDATE)ASWORDDATE,A

10、VG(SALARY1+SALARY2)ASAVG_SALARY4. FROMUSERWHEREDEGREEIN(3,4)5. GROUPBYDEGREE,YEAR(STARTWORKDATE)6. )ASG7. WHERE=8. ANDYEAR=9. AND(SALARY1+SALARY2);那使用WITH 和不使用 WITH，这两种写法有什么区别呢？一般情况下这两种写法在性能上不会有太大差异，但是，1、当USER表的记录很多2、硕士或博士（DEGREE IN (3,4)）在USER表中的比例很少当满足以上条件时，这两种写法在性能的差异将会显现出来，为什么呢？因为不使用WITH写法的语句访问了

11、2次USER表，如果DEGREE 字段又没有索引，性能差异将会非常明显。 当你看到这时，如果很好的理解了上面的内容，我相信你会对WITH语句有了一定的体会。然而WITH语句能做的还不止这些，下面给大家介绍一下，如何用WITH语句做递归查询。递归查询的一个典型的例子是对树状结构的表进行查询，考虑如下的情况：c-sharp1. 论坛首页2. -数据库开发3. -DB24. -DB2文章15. -DB2文章1的评论16. -DB2文章1的评论27. -DB2文章28. -Oracle9. -Java技术以上是一个论坛的典型例子，下面我们新建一个表来存储以上信息。c-sharp1. CREATETAB

12、LEBBS2. (3. PARENTIDINTEGERNOTNULL,4. IDINTEGERNOTNULL,5. NAMEVARCHAR(200)NOTNULL-板块、文章、评论等。6. );7. insertintobbs(PARENTID,ID,NAME)values8. (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,

13、Java技术);现在万事兼备了，我们开始查询吧。假设现在让你查询一下DB2 文章1的所有评论，有人说，这还不简单，如下这样就可以了。c-sharp1. SELECT*FROMBBSWHEREPARENTID=(SELECTIDFROMBBSWHERENAME=DB2);答案完全正确。那么，现在让你查询一下DB2的所有文章及评论，怎么办？传统的方法就很难查询了，这时候递归查询就派上用场了，如下：c-sharp1. WITHTEMP(PARENTID,ID,NAME)AS2. (3. SELECTPARENTID,ID,NAMEFROMBBSWHERENAME=DB2-语句14. UNIONALL

14、-语句25. SELECT,FROMBBSASB,TEMPASTWHERE=语句36. )7. SELECTNAMEFROMTEMP;-语句4运行后，我们发现，结果完全正确，那它到底是怎么运行的呢？下面我们详细讲解一下。1、首先，语句1将会执行，它只执行一次，作为循环的起点。得到结果集：DB22、接着，将循环执行语句3，这里我们有必要详细介绍一下。首先语句3的意图是什么呢？说白了，它就是查找语句1产生结果集（DB2）的下一级，那么在目录树中DB2的下一级是什么呢？是DB2 文章1和DB2 文章2，并且把查询到的结果集作为下一次循环的起点，然后查询它们的下一级，直到没有下一级为止。怎么样？还没明

15、白？哈哈，不要紧，我们一步一步来：首先，语句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临时集合中

16、得到我们期望的查询结果。怎么样，这回理解了吧，如果还没有理解，那么我也无能为力了。需要特别提醒的是1、一定要注意语句3的关联条件，否则很容易就写成死循环了。2、语句2必须是UNION ALL最后请大家猜想一下，把语句1的where子句去掉，将会产生什么样的结果呢？DB2递归实现字符串分组连接操作db2中的递归查询使用with来实现，也称为公共表达式，公共表达式在select语句的开始部分采用with子句的形式，在使用公共表达式的查询中可以多次使用它，并且公共表达式还可以通过取别名来连接到他本身，这样的话就可以达到循环的目的。递归查询通常有3个部分需要定义：一：一个公共表达式形式的虚拟表。二：一

17、个初始化表。三：一个与虚拟表进行完全内连接的辅助表。需要使用UNION all合并上边3个查询，然后用select从递归输出中得到最终的结果。大体上如下形式with XX(x1,x2,x3) as -0(select , from a -1union all -2select * from a,xx where = -3)select . from xx where . -40:为with体，即虚拟表1:为初始化表，这里需要定义初始化的一些行，也就是你递归的出发点，或者说父行，这部分逻辑只执行一次，它的结果作为虚拟表递归的初始化内容。2:这里必须用UNION all3:这里需要定义递归的条件（

18、辅助表），这里定义递归的逻辑，需要注意的是父行和子行进行连接的时候逻辑一定要清楚父子关系，不然很容易变成死循环的，这里首先将初始化表的结果作为条件进行查询，在把执行的结果添加到虚拟表中，只要这里能查询出来记录，那么就会进行下一步递归循环。4:这里就是对虚拟表的查询语句。需求：一张实时表，一个人每到一个地方，就有一条记录存在，存放样例：张三 上海张三 杭州.要求，按人名进行汇总，将他到过的地方拼接，中间用#分隔数据准备：-创建表：CREATE TABLE Recursive_Test(User_Name VARCHAR(12),City VARCHAR(12);-数据插入INSERT INTO

19、Recursive_Test(User_Name,City) values (张三,杭州),(张三,郑州),(李四,杭州), (张三,南昌),(李四,广州),(王五,北京);-递归实现：WITH Recursive_Test_Par(User_Name,City,Rk_Num) as(SELECT User_Name,City,ROW_NUMBER() OVER(PARTITION BY User_Name) -分组，生成序列，自我关联之用FROM Recursive_Test),City_Join(User_Name,City,R_Num) as(SELECT User_Name,CAST(

20、City AS VARCHAR(100),Rk_Num from Recursive_Test_Par WHERE Rk_Num=1UNION ALLSELECT ,CAST|#| AS VARCHAR(100),+1 from City_Join a1,Recursive_Test_Par b1WHERE = and =)SELECT , FROM City_Join a INNER JOIN (SELECT User_Name,max(R_Num) R_Num from City_Join GROUP BY User_Name) bON = and =;(另：WITH RECURSIVE_

21、TEST_PAR( USER_NAME, CITY, RK_NUM) AS( SELECT USER_NAME, CITY, ROW_NUMBER() OVER(PARTITION BY USER_NAME) FROM RECURSIVE_TEST),CITY_JOIN( USER_NAME, CITY, R_NUM)AS( SELECT USER_NAME, CAST(CITY AS VARCHAR(100), RK_NUM FROM RECURSIVE_TEST_PAR WHERE RK_NUM=1 UNION ALL SELECT , CAST AS VARCHAR(100)|#|, FROM RECURSIVE_TEST_PAR A, CITY_JOIN B WHERE = AND =) SELECT ,A. CITY FROM CITY_JOIN A INNER JOIN (SELECT USER_NAME,MAX(R_NUM) R_NUM FROM CITY_JOIN GROUP BY USER_NAME) BON = AND =-结果：王五北京李四杭州#广州张三杭州#郑州#南昌