Prayer

在一般中寻求卓越
posts - 1256, comments - 190, trackbacks - 0, articles - 0
  C++博客 :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

DB2 公共表表达式(WITH语句的使用)

Posted on 2010-05-18 13:21 Prayer 阅读(7287) 评论(2)  编辑 收藏 引用 所属分类: DB2

   说起WITH 语句,除了那些第一次听说WITH语句的人,大部分人都觉得它是用来做递归查询的。其实那只是它的一个用途而已,它的本名正如我们标题写的那样,叫做:公共表表达式(Common Table Expression),从字面理解,大家觉得它是用来干嘛的呢?其实,它是用来定义临时集合的。啊?VALUES语句不是用来定义临时集合的吗?怎么WITH语句也用来定义临时集合呢?它们有什么区别呢?

VALUES语句是用明确的值来定义临时集合的,如下:

VALUES (1,2), (1,3),(2,1) 
VALUES (1,2), (1,3),(2,1)

WITH语句是用查询(也就是select语句)来定义临时集合的,从这个角度讲,有点像视图,不过不是视图,大家千万别误解。如下:

CREATE TABLE USER (  
NAME VARCHAR(20) NOT NULL,---姓名  
SEX INTEGER,---性别(1、男   2、女)  
BIRTHDAY DATE---生日  
); 
CREATE TABLE USER (
NAME VARCHAR(20) NOT NULL,---姓名
SEX INTEGER,---性别(1、男 2、女)
BIRTHDAY DATE---生日
);

WITH TEST(NAME_TEST, BDAY_TEST) AS  
(  
SELECT NAME,BIRTHDAY FROM USER--语句1  
)  
SELECT NAME_TEST FROM TEST WHERE BDAY_TEST='1949-10-1';--语句2 
WITH TEST(NAME_TEST, BDAY_TEST) AS
(
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 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语句是为复杂的查询为设计的,的确是这样的,下面我们举个复杂的例子,想提高技术的朋友可千万不能错过。考虑下面的情况:

CREATE TABLE USER  
(  
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--奖金  
); 
CREATE TABLE USER
(
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--奖金
);

假设现在让你查询一下那些 1、学历是硕士或博士  2、学历相同,入职年份也相同,但是工资(基本工资+奖金)却比相同条件员工的平均工资低的员工。(哈哈,可能是要涨工资),不知道你听明白问题没有?该怎么查询呢?我们是这样想的:

1、查询学历是硕士或博士的那些员工得到结果集1,如下:

SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN (3,4); 
SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN (3,4);

2、根据学历和入职年份分组,求平均工资 得到结果集2,如下:

SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY   
FROM USER WHERE DEGREE IN (3,4)  
GROUP BY DEGREE,YEAR(STARTWORKDATE) 
SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY
FROM USER 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 USER WHERE DEGREE IN (3,4)  
),  
TEMP2 (DEGREE,WORDDATE,AVG_SALARY) AS  
(  
SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY   
FROM USER 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; 
WITH TEMP1(NAME,DEGREE,WORDDATE,SALARY) AS
(
SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER WHERE DEGREE IN (3,4)
),
TEMP2 (DEGREE,WORDDATE,AVG_SALARY) AS
(
SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY
FROM USER 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 USER 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 TEMP1(NAME,DEGREE,WORDDATE,SALARY) AS
(
SELECT NAME,DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, SALARY1+SALARY2 AS SALARY FROM USER 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 USER AS U,  
(  
SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY   
FROM USER 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; 
SELECT U.NAME FROM USER AS U,
(
SELECT DEGREE,YEAR(STARTWORKDATE) AS WORDDATE, AVG(SALARY1+SALARY2) AS AVG_SALARY
FROM USER 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 字段又没有索引,性能差异将会非常明显。

    当你看到这时,如果很好的理解了上面的内容,我相信你会对WITH语句有了一定的体会。然而WITH语句能做的还不止这些,下面给大家介绍一下,如何用WITH语句做递归查询。递归查询的一个典型的例子是对树状结构的表进行查询,考虑如下的情况:

论坛首页  
--数据库开发  
----DB2  
------DB2 文章1  
--------DB2 文章1 的评论1  
--------DB2 文章1 的评论2  
------DB2 文章2  
----Oracle  
--Java技术 
论坛首页
--数据库开发
----DB2
------DB2 文章1
--------DB2 文章1 的评论1
--------DB2 文章1 的评论2
------DB2 文章2
----Oracle
--Java技术

以上是一个论坛的典型例子,下面我们新建一个表来存储以上信息。

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技术'); 
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'); 
SELECT * FROM BBS WHERE PARENTID=(SELECT ID FROM BBS WHERE NAME='DB2');

答案完全正确。那么,现在让你查询一下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 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

运行后,我们发现,结果完全正确,那它到底是怎么运行的呢?下面我们详细讲解一下。

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子句去掉,将会产生什么样的结果呢?

 


---更多参见:DB2 SQL 精萃

----声明:转载请注明出处。

----last updated at 2010.1.27

----written by wave at 2009.9.27

----end

 

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/shangboerds/archive/2009/09/27/4601023.aspx

Feedback

# re: DB2 公共表表达式(WITH语句的使用)[未登录]  回复  更多评论   

2016-03-09 20:26 by kk
查询一下‘DB2 文章1’的所有评论应该是这样吧?SELECT * FROM BBS WHERE PARENTID=(SELECT ID FROM BBS WHERE NAME='DB2 文章1’');

# re: DB2 公共表表达式(WITH语句的使用)[未登录]  回复  更多评论   

2016-03-09 20:32 by kk
查询一下DB2的所有文章及评论,语句1应该是这样写吧?
SELECT PARENTID,ID,NAME FROM BBS WHERE PARENTID=(SELECT ID FROM BBS WHERE NAME='DB2'); ---语句1
这样的结果才是得到DB2的下一级数据。

正在学习CTE,感觉不是很理解就提出来,不对还望指正,共同学习进步,谢谢!

只有注册用户登录后才能发表评论。
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理