软件世界网 购物 网址 三丰软件 | 小说 美女秀 图库大全 游戏 笑话 | 下载 开发知识库 新闻 开发 图片素材
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
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年7日历
2018-7-19 13:42:39
多播视频美女直播
↓电视,电影,美女直播,迅雷资源↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  软件世界网 --