软件世界网 购物 网址 三丰软件 | 小说 美女秀 图库大全 游戏 笑话 | 下载 开发知识库 新闻 开发 图片素材
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
移动开发 架构设计 编程语言 Web前端 互联网
开发杂谈 系统运维 研发管理 数据库 云计算 Android开发资料
  软件世界网 -> 数据库 -> DB2with的定义与用法 -> 正文阅读

[数据库]DB2with的定义与用法


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、以学历和入职年份为条件 联合两个结果集,查找工资<平均工资 的员工,以下是完整的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写法的语句访问了2次USER表,
如果DEGREE 字段又没有索引,性能差异将会非常明显。
2.with的递归应用一
当你看到这时,如果很好的理解了上面的内容,我相信你会对WITH语句有了一定的体会。然而WITH语句能做的还不止这些,
下面给大家介绍一下,如何用WITH语句做递归查询。递归查询的一个典型的例子是对树状结构的表进行查询,考虑如下的情况:
01.论坛首页  
02.--数据库开发  
03.----DB2  
04.------DB2 文章1  
05.--------DB2 文章1 的评论1  
06.--------DB2 文章1 的评论2  
07.------DB2 文章2  
08.----Oracle  
09.--Java技术  
 
以上是一个论坛的典型例子,下面我们新建一个表来存储以上信息。
drop table BBS;
CREATE TABLE BBS  
(  
PARENTID INTEGER NOT NULL,  
ID INTEGER NOT NULL,  
NAME VARCHAR(200) NOT NULL---板块、文章、评论等。  
);  
insert into bbs (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 * FROM BBS WHERE PARENTID=(SELECT ID FROM BBS WHERE NAME='DB2 文章1');  
 
答案完全正确。那么,现在让你查询一下DB2的所有文章及评论,怎么办?传统的方法就很难查询了,这时候递归查询就派上用场了,如下:
 
WITH TEMP(PARENTID,ID,NAME) AS  
(  
SELECT PARENTID,ID,NAME FROM BBS WHERE NAME='DB2' ---语句1  
UNION ALL ---语句2 
SELECT B.PARENTID,B.ID,B.NAME FROM BBS AS B, TEMP AS T WHERE B.PARENTID=T.ID ---语句3  
)  
SELECT NAME FROM TEMP; ---语句4  
WITH 子句内的第一个 SELECT 语句是初始化表。它只执行一次。它的结果形成虚拟表的初始内容以作为递归的种子。在上面的示例中,种子是 'NAME' 为  DB2的一行或多行。 
第二个 SELECT 语句执行多次。将种子作为输入传递给第二个 SELECT 语句以产生下一个行集合。将结果添加(UNION ALL)到虚拟表的当前内容中,并放回到其中以形成用于下一次传递的输入。只要有行产生,这个过程就会继续。 
运行后,我们发现,结果完全正确,那它到底是怎么运行的呢?下面我们详细讲解一下。
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必须是 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
[img]http://s14.sinaimg.cn/mw690/70daf0b6gdf0428532d4d&690
临时表t里面,首先执行语句1.取得根结果集,这是循环的基础。注意:语句1只执行一次。
[img]http://s13.sinaimg.cn/mw690/70daf0b6gdf042977c71c&690
第一次循环传入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值最大的记录,
得到最终所期望的行转列结果集:
[img]http://s11.sinaimg.cn/mw690/70daf0b6g7cb39de171aa&690

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;
......显示全文...
    点击查看全文


上一篇文章      下一篇文章      查看所有文章
2016-03-26 16:27:14  
数据库 最新文章
Python&MySQL&PyQt
最新用python来操作mysql完全解析
mongodb的安装详解
1.PDO简介
《MySQL必知必会学习笔记》:高级联结
【翻译自mos文章】怎么对Microsoft(Office)
MyCAT全局表描述及示例
ocp
关于SQL数据表存储过程表名前缀换成dbo代码
数据库调优教程(二)慢查询数据准备
360图书馆 软件开发资料 文字转语音 购物精选 软件下载 新闻资讯 小游戏 Chinese Culture 股票 三丰软件 开发 中国文化 网文精选 阅读网 看图 日历 万年历 2018年11日历
2018-11-14 19:00:05
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  软件世界网 --