《DB2with的定义与用法 - DB2 -- 电脑知识与技术互动沟通平台.docx》由会员分享，可在线阅读，更多相关《DB2with的定义与用法 - DB2 -- 电脑知识与技术互动沟通平台.docx（13页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、DB2with的定义与用法-DB2-次元立方网-电脑知识与技术互动沟通平台With定义与用法-部分内容为转载并经整理处理-1.with理解与基本用法讲起WITH语句，除了那些第一次听讲WITH语句的人，大部分人都觉得它是用来做递归查询的。其实那只是它的一个用处罢了，它的本名正如我们标题写的那样，叫做：公共表表达式CommonTableExpression，从字面理解，大家觉得它是用来干嘛的呢？其实，它是用来定义临时集合的。啊？VALUES语句不是用来定义临时集合的吗？怎么WITH语句也用来定义临时集合呢？它们有什么区别呢？VALUES语句是用明确的值来定义临时集合的，如下：values(1,2
2、),(1,3),(2,1)WITH语句是用查询(也就是select语句)来定义临时集合的，从这个角度讲，有点像视图，不过不是视图，大家千万别误解。例如下：-建表DROPTABLEUSER;CREATETABLEUSER(NAMEVARCHAR(20)NOTNULL,-姓名SEXINTEGER,-性别1、男2、女BIRTHDAYDATE-生日);-插数据insertintouser(name,sex,birthday)values(zhangshan,1,1990-1-1insertintouser(name,sex,birthday)values(lisi,2,1991-1-1insertin
3、touser(name,sex,birthday)values(wangwu,1,1992-1-1insertintouser(name,sex,birthday)values(sunliu,2,1949-10-1insertintouser(name,sex,birthday)values(tianqi,1,1994-1-1insertintouser(name,sex,birthday)values(zhaoba,2,1995-1-1WITHTEST(NAME_TEST,BDAY_TEST)AS-test是括号中查询出来的结果集命名，后接重命名列(SELECTNAME,BIRTHDAYFR
4、OMUSER-语句1)SELECTNAME_TESTFROMTESTWHEREBDAY_TEST=1949-10-1-语句2下面我们来解释一下，首先语句1执行，它会产生一个有两列NAME,BIRTHDAY的结果集；接着，我们将这个结果集命名为test，并且将列名重命名为NAME_TEST,BDAY_TEST；最后我们执行语句2，从这个临时集合中找到生日是1949-10-1，也就是共和国的同龄人。怎么样？假如你感觉不好理解，请仔细的分析一下上面的语句。下面我们举个VALUES语句和WITH语句结合使用的例子，如下：WITHTEST(NAME_TEST,BDAY_TEST)AS(VALUES(张三
5、,1997-7-1),(李四,1949-10-1)SELECTNAME_TESTFROMTESTWHEREBDAY_TEST=1949-10-1从上面的介绍和WITH语句不为大多数人所熟悉能够猜想，WITH语句是为复杂的查询为设计的，确实是这样的，下面我们举个复杂的例子，想提高技术的朋友可千万不能错过。考虑下面的情况：-建表DROPTABLEUSER2;CREATETABLEUSER2(NAMEVARCHAR(20)NOTNULL,-姓名DEGREEINTEGERNOTNULL,-学历(1、专科2、本科3、硕士4、博士)STARTWORKDATEdateNOTNULL,-入职时间SALARY1
6、FLOATNOTNULL,-基本工资SALARY2FLOATNOTNULL-奖金);-插数据insertintouser2(name,degree,startworkdate,salary1,salary2)values(zhangsan,1,1995-1-1,10000.00,1600.00);insertintouser2(name,degree,startworkdate,salary1,salary2)values(lisi,2,1996-1-1,5000.00,1500.00);insertintouser2(name,degree,startworkdate,salary1,sal
7、ary2)values(wangwu,3,1997-1-1,6000.00,1400.00);insertintouser2(name,degree,startworkdate,salary1,salary2)values(sunliu,4,1998-1-1,7000.00,1300.00);insertintouser2(name,degree,startworkdate,salary1,salary2)values(tianqi,2,1999-1-1,7000,1300insertintouser2(name,degree,startworkdate,salary1,salary2)val
8、ues(zhaoba,1,2000-1-1,9000,1400);insertintouser2(name,degree,startworkdate,salary1,salary2)values(qianjiu,3,1997-1-1,2000,1000);insertintouser2(name,degree,startworkdate,salary1,salary2)values(dushe,4,1992-1-1,3000,1000);select*fromuser2;假设如今让你查询一下那些1、学历是硕士或博士2、学历一样，入职年份也一样，但是工资基本工资+奖金却比一样条件员工的平均工资低
9、的员工。哈哈，可能是要涨工资，不知道你听明白问题没有？该怎么查询呢？我们是这样想的：1、查询学历是硕士或博士的那些员工得到结果集1，如下：SELECTNAME,DEGREE,YEAR(STARTWORKDATE)ASWORDDATE,SALARY1+SALARY2ASSALARYFROMUSER2WHEREDEGREEIN(3,4);2、根据学历和入职年份分组，求平均工资得到结果集2，如下：SELECTDEGREE,YEAR(STARTWORKDATE)ASWORDDATE,AVG(SALARY1+SALARY2)ASAVG_SALARYFROMUSER2WHEREDEGREEIN(3,4)G
10、ROUPBYDEGREE,YEAR(STARTWORKDATE);3、以学历和入职年份为条件联合两个结果集，查找工资平均工资的员工，下面是完好的SQL：WITHTEMP1(NAME,DEGREE,WORDDATE,SALARY)AS(SELECTNAME,DEGREE,YEAR(STARTWORKDATE)ASWORDDATE,SALARY1+SALARY2ASSALARYFROMUSER2WHEREDEGREEIN(3,4),TEMP2(DEGREE,WORDDATE,AVG_SALARY)AS(SELECTDEGREE,YEAR(STARTWORKDATE)ASWORDDATE,AVG(S
11、ALARY1+SALARY2)ASAVG_SALARYFROMUSER2WHEREDEGREEIN(3,4)GROUPBYDEGREE,YEAR(STARTWORKDATE)SELECTNAMEFROMTEMP1,TEMP2WHERETEMP1.DEGREE=TEMP2.DEGREEANDTEMP1.WORDDATE=TEMP2.WORDDATEANDSALARYAVG_SALARY;查询结果完全正确，但我们还有改善的空间，在查询结果集2的时候，我们是从user表中获得数据的。其实此时结果集1已经查询出来了，我们完全能够从结果集1中通过分组得到结果集2，而不用从uer表中得到结果集2，比拟上面
12、和下面的语句你就能够知道我讲的是什么意思了！WITHTEMP1(NAME,DEGREE,WORDDATE,SALARY)AS(SELECTNAME,DEGREE,YEAR(STARTWORKDATE)ASWORDDATE,SALARY1+SALARY2ASSALARYFROMUSER2WHEREDEGREEIN(3,4),TEMP2(DEGREE,WORDDATE,AVG_SALARY)AS(SELECTDEGREE,WORDDATE,AVG(SALARY)ASAVG_SALARYFROMTEMP1GROUPBYDEGREE,WORDDATE)SELECTNAMEFROMTEMP1,TEMP2
13、WHERETEMP1.DEGREE=TEMP2.DEGREEANDTEMP1.WORDDATE=TEMP2.WORDDATEANDSALARYAVG_SALARY;可能有些朋友会讲，我不用WITH语句可以以查出来，确实是这样，如下：SELECTU.NAMEFROMUSER2ASU,(SELECTDEGREE,YEAR(STARTWORKDATE)ASWORDDATE,AVG(SALARY1+SALARY2)ASAVG_SALARYFROMUSER2WHEREDEGREEIN(3,4)GROUPBYDEGREE,YEAR(STARTWORKDATE)ASGWHEREU.DEGREE=G.DEGR
14、EEANDYEAR(U.STARTWORKDATE)=G.WORDDATEAND(SALARY1+SALARY2)G.AVG_SALARY;那使用WITH和不使用WITH，这两种写法有什么区别呢？一般情况下这两种写法在性能上不会有太大差异，但是，1、当USER表的记录很多2、硕士或博士DEGREEIN(3,4)在USER表中的比例很少当知足以上条件时，这两种写法在性能的差异将会显现出来，为什么呢？由于不使用WITH写法的语句访问了2次USER表，假如DEGREE字段又没有索引，性能差异将会非常明显。2.with的递归应用一当你看到这时，假如很好的理解了上面的内容，我相信你会对WITH语句有了一
15、定的体会。然而WITH语句能做的还不止这些，下面给大家介绍一下，怎样用WITH语句做递归查询。递归查询的一个典型的例子是对树状构造的表进行查询，考虑如下的情况：01.论坛首页02.-数据库开发03.-DB204.-DB2文章105.-DB2文章1的评论106.-DB2文章1的评论207.-DB2文章208.-Oracle09.-Java技术以上是一个论坛的典型例子，下面我们新建一个表来存储以上信息。droptableBBS;CREATETABLEBBS(PARENTIDINTEGERNOTNULL,IDINTEGERNOTNULL,NAMEVARCHAR(200)NOTNULL-板块、文章、评
16、论等。);insertintobbs(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*FROMBBSWHEREPARENTID=(SELECTIDFROMBBSWHERENAME=DB2文章1答案完全正确
17、。那么，如今让你查询一下DB2的所有文章及评论，怎么办？传统的方法就很难查询了，这时候递归查询就派上用场了，如下：WITHTEMP(PARENTID,ID,NAME)AS(SELECTPARENTID,ID,NAMEFROMBBSWHERENAME=DB2-语句1UNIONALL-语句2SELECTB.PARENTID,B.ID,B.NAMEFROMBBSASB,TEMPASTWHEREB.PARENTID=T.ID-语句3)SELECTNAMEFROMTEMP;-语句4WITH子句内的第一个SELECT语句是初始化表。它只执行一次。它的结果构成虚拟表的初始内容以作为递归的种子。在上面的示例中
18、，种子是NAME为DB2的一行或多行。第二个SELECT语句执行屡次。将种子作为输入传递给第二个SELECT语句以产生下一个行集合。将结果添加UNIONALL到虚拟表的当前内容中，并放回到其中以构成用于下一次传递的输入。只要有行产生，这个经过就会继续。运行后，我们发现，结果完全正确，那它到底是怎么运行的呢？下面我们具体讲解一下。1、首先，语句1将会执行，它只执行一次，作为循环的起点。得到结果集：DB22、接着，将循环执行语句3，这里我们有必要具体介绍一下。首先语句3的意图是什么呢？讲白了，它就是查找语句1产生结果集DB2的下一级，那么在目录树中DB2的下一级是什么呢？是DB2文章1和DB2文章
19、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，将所有的结果集放在一起，最终得
20、到temp结果集。4、最后，我们通过语句4从temp临时集合中得到我们期望的查询结果。需要十分提醒的是1、一定要注意语句3的关联条件，否则很容易就写成死循环了。2、语句2必须是UNIONALL最后请大家猜测一下，把语句1的where子句去掉，将会产生什么样的结果呢？去掉where后将全是死循环，由于每次查出的结果集都是全查的记录，而且永远都是。3.with递归应用二，行转列-1.建表droptablezxt_testcreatetablezxt_test(idvarchar(10),ivaluevarchar(20),inamevarchar(20);commit;select*fromzxt
21、_test;-2.插入测试语句insertintozxt_testvalues(1,aa,x),(2,bb,x),(3,bb,x),(1,bb,y),(2,bb,y),(3,bb,ycommit;withsas(-这里是用iname来分区，id来排序。假如表没有这样序号分明的id字段，能够用rowNum()生成序号selectrow_number()over(partitionbyinameorderbyid)id1,row_number()over(partitionbyinameorderbyid)id2,ivalue,inamefromzxt_test),t(iname,id1,id2,
22、ivalue)as(selectiname,id1,id2,cast(ivalueasvarchar(100)fromswhereid1=1andid2=1-语句1unionallselectt.iname,t.id1+1,t.id2,cast(s.ivalue|,|t.ivalueasvarchar(100)-语句2froms,twheres.id2=t.id1+1andt.iname=s.iname)-wheres.iname=t.iname能够去掉，不影响selectiname,ivaluefromtwheret.id1=(selectmax(id1)fromswheres.iname=
23、t.iname);-语句3结果集s临时表t里面，首先执行语句1.获得根结果集，这是循环的基础。注意:语句1只执行一次。第一次循环传入t到语句2获取到的结果集是:这时联合的结果集临时表t是:(V为加豆号后的值)INAMEt.id1+1t.id2VINAMEt.id1t.id2VX21bb,aaX11aaY21bb,bbY11bbX21bb,aaY21bb,bb第二次循环传入t到语句2获取到的结果集是:这时联合的最终结果集临时表t是:INAMEt.id1+1t.id2VINAMEt.id1t.id2VX31bb,bb,aaX11aaY31bb,bb,bbY11bbX21bb,aaY21bb,bbX
24、31bb,bb,aaY31bb,bb,bb在语句3加条件可取最终结果集临时表t中id1值最大的记录，得到最终所期望的行转列结果集:例DB2行转列：不确定有多少行的情况实现思路，先递归，然后排序，取第一行。withrsas(selectbbd043,row_number()over()RNfrombb72wherebae007=10001),RPL(RN,bbd043)as(selectROOT.RN,CAST(ROOT.bbd043asvarchar(2000)fromrsROOTUNIONALLSELECTCHILD.RN,CHILD.bbd043|,|PARENT.bbd043FROMRP
25、LPARENT,rsCHILDWHEREPARENT.RN+1=CHILD.RN)SELECTMAX(bbd043)bbd043FROMRPLGROUPBYRNORDERBYRNDESCFETCHFIRST1ROWSONLY;DB2中行转列效率比拟效率高，可应付大数据量withsas(selectrow_number()over()id1,row_number()over()id2,AAE004fromBB20whereAAE004sql01),t(id1,id2,AAE004)as(selectid1,id2,AAE004fromswhereid1=1andid2=1unionallsele
26、ctt.id1+1,t.id2,cast(s.AAE004|,|t.AAE004asvarchar(20000)froms,twheres.id2=t.id1+1)selectAAE004fromtwheret.id1=(selectmax(id1)froms);效率差，数据量一大，就屌丝了withrsas(selectAAE004,row_number()over()RNfromBB20whereAAE004sql02),RPL(RN,AAE004)as(selectROOT.RN,CAST(ROOT.AAE004asvarchar(20000)fromrsROOTUNIONALLSELECTCHILD.RN,CHILD.AAE004|,|PARENT.AAE004FROMRPLPARENT,rsCHILDWHEREPARENT.RN+1=CHILD.RN)SELECTRPL.RN,MAX(AAE004)AAE004FROMRPLGROUPBYRNORDERBYRNDESCFETCHFIRST1ROWSONLY;