DB2with的界定与使用方法

摘要: With界定与使用方法-------一部分內容为转截并经梳理解决--------------------- 1.with了解与基本使用方法 谈起WITH 句子,除开这些第一次听闻WITH句子的人,大部分分人都感觉它是用于做递归查...

With界定与使用方法
-------一部分內容为转截并经梳理解决--------------------- 1.with了解与基本使用方法 谈起WITH 句子,除开这些第一次听闻WITH句子的人,大部分分人都感觉它是用于做递归查寻的。实际上那仅仅它的一个主要用途罢了, 它的本名如同大家题目写的那般,称为:公共性表表述式(Common Table Expression),从字面上了解,大伙儿感觉它是用于干什么的呢? 实际上,它是用于界定临时性结合的。啊?VALUES句子并不是用于界定临时性结合的吗?如何WITH句子也用于界定临时性结合呢?他们有哪些差别呢?
VALUES句子是用确立的值而定义临时性结合的,以下: values (1,2), (1,3),(2,1) WITH句子是用查寻(也便是select句子)而定义临时性结合的,从这一视角讲,有点儿像主视图,但是并不是主视图,大伙儿干万别误会。比如下:
--建表 DROP TABLE USER; CREATE TABLE USER ( NAME VARCHAR(20) NOT NULL,---名字 SEX INTEGER,---性別(1、男 2、女) BIRTHDAY DATE---生辰 ); --插数据信息 insert into user (name,sex,birthday) values ( zhangshan , 1 , 1990-1-1 insert into user (name,sex,birthday) values ( lisi , 2 , 1991-1-1 insert into user (name,sex,birthday) values ( wangwu , 1 , 1992-1-1 insert into user (name,sex,birthday) values ( sunliu , 2 , 1949-10-1 insert into user (name,sex,birthday) values ( tianqi , 1 , 1994-1-1 insert into user (name,sex,birthday) values ( zhaoba , 2 , 1995-1-1
WITH TEST(NAME_TEST, BDAY_TEST) AS --test是括弧中查寻出去的結果集取名,后接重新命名列 ( SELECT NAME,BIRTHDAY FROM USER--句子1 ) 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句子融合应用的事例,以下:
WITH TEST(NAME_TEST, BDAY_TEST) AS ( VALUES ( 张三 , 1997-7-1 ),( 李四 , 1949-10-1 ) ) SELECT NAME_TEST FROM TEST WHERE BDAY_TEST= 1949-10-1
从上边的详细介绍和WITH句子不以大多数数人所了解能够猜想,WITH句子是为繁杂的查寻为设计方案的,确实是那样的, 下边大家举个繁杂的事例,想提升技术性的朋友可干万不可以错过了。考虑到下边的状况:
--建表 DROP TABLE USER2; CREATE TABLE USER2 ( NAME VARCHAR(20) NOT NULL,--名字 DEGREE INTEGER NOT NULL,--文凭(1、大专 2、大学本科 3、研究生 4、博士研究生) STARTWORKDATE date NOT NULL,--新员工入职時间 SALARY1 FLOAT NOT NULL,--标准工资 SALARY2 FLOAT NOT NULL--奖励金 );
--插数据信息 insert into user2 (name,degree,startworkdate,salary1,salary2) values ( zhangsan ,1, 1995-1-1 ,10000.00,1600.00); insert into user2 (name,degree,startworkdate,salary1,salary2) values ( lisi ,2, 1996-1-1 ,5000.00,1500.00); insert into user2 (name,degree,startworkdate,salary1,salary2) values ( wangwu ,3, 1997-1-1 ,6000.00,1400.00); insert into user2 (name,degree,startworkdate,salary1,salary2) values ( sunliu ,4, 1998-1-1 ,7000.00,1300.00); insert into user2 (name,degree,startworkdate,salary1,salary2) values ( tianqi ,2, 1999-1-1 , 7000 , 1300 insert into user2 (name,degree,startworkdate,salary1,salary2) values ( zhaoba ,1, 2000-1-1 ,9000,1400); insert into user2 (name,degree,startworkdate,salary1,salary2) values ( qianjiu ,3, 1997-1-1 ,2000,1000); insert into user2 (name,degree,startworkdate,salary1,salary2) values ( dushe ,4, 1992-1-1 ,3000,1000); select * from user2;
假定如今给你查寻一下这些 1、文凭是研究生或博士研究生 2、文凭同样,新员工入职年代也同样,可是薪水(标准工资+奖励金)却比同样标准职工的均值薪水低的职工。 嘿嘿,将会是要涨薪水),不知道道你听搞清楚难题沒有?该如何查寻呢?大家是那样想的:
1、查寻文凭是研究生或博士研究生的这些职工获得結果集1,以下: SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE,SALARY1+SALARY2 AS SALARY FROM USER2 WHERE DEGREE IN (3,4);
2、依据文凭和新员工入职年代排序,求均值薪水 获得結果集2,以下: SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY FROM USER2 WHERE DEGREE IN (3,4) GROUP BY DEGREE,YEAR(STARTWORKDATE);
3、以文凭和新员工入职年代为标准 协同2个結果集,搜索薪水 均值薪水 的职工,下列是详细的SQL: WITH TEMP1(NAME,DEGREE,WORDDATE,SALARY) AS ( SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER2 WHERE DEGREE IN (3,4) ), TEMP2 (DEGREE,WORDDATE,AVG_SALARY) AS ( SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY FROM USER2 WHERE DEGREE IN (3,4) GROUP BY DEGREE,YEAR(STARTWORKDATE) ) SELECT NAME FROM TEMP1, TEMP2 WHERE TEMP1.DEGREE=TEMP2.DEGREE AND TEMP1.WORDDATE=TEMP2.WORDDATE AND SALARY AVG_SALARY;
查寻結果彻底恰当,但大家也有改进的室内空间,在查寻結果集2的情况下,大家是以user表格中获得数据信息的。 实际上这时結果集1早已查寻出去了,大家彻底能够从結果集1中通快递太过组获得結果集2, 而无需从uer表格中获得結果集2,较为上边和下边的句子你也就能够了解我讲的是啥含意了! WITH TEMP1(NAME,DEGREE,WORDDATE,SALARY) AS ( SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER2 WHERE DEGREE IN (3,4) ), TEMP2 (DEGREE,WORDDATE,AVG_SALARY) AS ( SELECT DEGREE,WORDDATE, AVG(SALARY) AS AVG_SALARY FROM TEMP1 GROUP BY DEGREE,WORDDATE ) SELECT NAME FROM TEMP1, TEMP2 WHERE TEMP1.DEGREE=TEMP2.DEGREE AND TEMP1.WORDDATE=TEMP2.WORDDATE AND SALARY AVG_SALARY; 将会一些朋友要说,我不会用WITH句子还可以查出来来,确实是那样,以下: SELECT U.NAME FROM USER2 AS U, ( SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY FROM USER2 WHERE DEGREE IN (3,4) GROUP BY DEGREE,YEAR(STARTWORKDATE) ) AS G WHERE U.DEGREE=G.DEGREE AND YEAR(U.STARTWORKDATE)=G.WORDDATE AND (SALARY1+SALARY2) G.AVG_SALARY;
那应用WITH 和不应用 WITH,这二种书写有哪些差别呢?一般状况下这二种书写在特性上不容易有很大差别,可是, 1、当USER表的纪录许多 2、研究生或博士研究生(DEGREE IN (3,4))在USER表格中的占比非常少
当考虑之上标准时,这二种书写在特性的差别可能呈现出去,为何呢?由于不应用WITH书写的句子浏览了两次USER表, 假如DEGREE 字段名又沒有数据库索引,特性差别可能十分显著。
2.with的递归运用一 如果你见到这时候,假如非常好的了解了上边的內容,相信你能对WITH句子拥有一定的感受。但是WITH句子能做的还不仅这种, 下边给大伙儿详细介绍一下,怎样用WITH句子做递归查寻。递归查寻的一个典型性的事例是对树形结构构造的表开展查寻,考虑到以下的状况:
WITH 子句内的第一个 SELECT 句子是原始化表。它只实行一次。它的結果产生虚似表的原始內容以做为递归的種子。在上边的实例中,種子是 NAME 为 DB2的一行或几行。
第二个 SELECT 句子实行数次。将種子做为键入传送给第二个 SELECT 句子以造成下一个行结合。将結果加上(UNION ALL)到虚似表确当前內容中,并放返回在其中以产生用以下一次传送的键入。要是有行造成,这一全过程便会再次。
必须非常提示的是 1、一定要留意句子3的关系标准,不然非常容易就写出死循环系统了。 2、句子2务必是 UNION ALL 最终请大伙儿猜测一下,把句子1的where子句除掉,可能造成哪些的結果呢?除掉where后将都是死循环系统,由于每一次查出来的結果集全是全查的纪录,并且始终全是。
3.with递归运用二,行转列 --1.建表 drop table zxt_test create table zxt_test ( id varchar(10), ivalue varchar(20), iname varchar(20) ); commit; select * from zxt_test; ----------- ---2.插进检测句子 insert into zxt_test values( 1 , aa , x ),( 2 , bb , x ),( 3 , bb , x ),( 1 , bb , y ),( 2 , bb , y ),( 3 , bb , y commit;
with s as ( --这儿是用iname来系统分区,id来排列。假如表沒有那样编号明晰的id字段名,能够用rowNum()转化成编号 select row_number()over(partition by iname order by id) id1, row_number()over(partition by iname order by id) id2, ivalue,iname from zxt_test ), t(iname,id1,id2,ivalue) as ( select iname,id1,id2,cast(ivalue as varchar(100)) from s where id1 =1 and id2=1 --句子1 union all select t.iname,t.id1+1,t.id2,cast(s.ivalue|| , ||t.ivalue as varchar(100)) --句子2 from s, t where s.id2=t.id1+1 and t.iname = s.iname ) --where s.iname = t.iname能够除掉,不危害 select iname,ivalue from t where t.id1= (select max(id1) from s where s.iname = t.iname); --句子3 結果集s DB2 wbr with的定义与用法
临时性表t里边,最先实行句子1.获得根結果集,它是循环系统的基本。留意:句子1只实行一次。 DB2 wbr with的定义与用法
第一次循环系统传到t到句子2获得到的結果集是: 这时候协同的結果集临时性表t是:(V为加豆号后的值) INAME t.id1+1 t.id2 V INAME t.id1 t.id2 V X 2 1 bb,aa X 1 1 aa Y 2 1 bb,bb Y 1 1 bb X 2 1 bb,aa Y 2 1 bb,bb
第二次循环系统传到t到句子2获得到的結果集是: 这时候协同的最后結果集临时性表t是: INAME t.id1+1 t.id2 V INAME t.id1 t.id2 V X 3 1 bb,bb,aa X 1 1 aa Y 3 1 bb,bb,bb Y 1 1 bb X 2 1 bb,aa Y 2 1 bb,bb X 3 1 bb,bb,aa Y 3 1 bb,bb,bb 在句子3加标准可用最后結果集临时性表t中id1值较大的纪录, 获得最后所期待的行转列結果集: DB2 wbr with的定义与用法
例 DB2行转列:(不确定性有是多少行的状况) 完成构思,先递归,随后排列,取第一行。 with rs as (select bbd043,row_number() over() RN from bb72 where bae007= 10001 ), RPL(RN,bbd043) as ( select ROOT.RN,CAST(ROOT.bbd043 as varchar(2000)) from rs ROOT UNION ALL SELECT CHILD.RN,CHILD.bbd043|| , ||PARENT.bbd043 FROM RPL PARENT,rs CHILD WHERE PARENT.RN+1=CHILD.RN ) SELECT MAX(bbd043) bbd043 FROM RPL GROUP BY RN ORDER BY RN DESC FETCH FIRST 1 ROWS ONLY;
DB2中国银行转列高效率较为 高效率高,可应对绝大多数据量 with s as ( select row_number()over() id1, row_number()over() id2, AAE004 from BB20 where AAE004 ------sql01 ), t(id1,id2,AAE004) as ( select id1,id2,AAE004 from s where id1 =1 and id2=1 union all select t.id1+1,t.id2,cast(s.AAE004|| , ||t.AAE004 as varchar(20000)) from s, t where s.id2=t.id1+1 ) select AAE004 from t where t.id1= (select max(id1) from s );
高效率差,数据信息量一大,就吊丝了 with rs as (select AAE004,row_number() over() RN from BB20 where AAE004 ------sql02), RPL(RN,AAE004) as ( select ROOT.RN,CAST(ROOT.AAE004 as varchar(20000)) from rs ROOT UNION ALL SELECT CHILD.RN,CHILD.AAE004|| , ||PARENT.AAE004 FROM RPL PARENT,rs CHILD WHERE PARENT.RN+1=CHILD.RN ) SELECT RPL.RN,MAX(AAE004) AAE004 FROM RPL GROUP BY RN ORDER BY RN DESC FETCH FIRST 1 ROWS ONLY;


联系我们

全国服务热线:4000-399-000 公司邮箱:343111187@qq.com

  工作日 9:00-18:00

关注我们

官网公众号

官网公众号

Copyright?2020 广州凡科互联网科技股份有限公司 版权所有 粤ICP备10235580号 客服热线 18720358503

技术支持:图片修改文字软件