DB2 公共表表达式.doc

DB2 公共表表达式（WITH语句的使用） Posted on 2010-05-18 13:21 Prayer 阅读(477) 评论(0) 编辑收藏 引用 所属分类: 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 SALARYAVG_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 SALARYAVG_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 SALARYAVG_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 TEMP1GROUP BY DEGREE,WORDDATE)SELECT NAME FROM TEMP1, TEMP2 WHERE TEMP1.DEGREE=TEMP2.DEGREE AND TEMP1.WORDDATE=TEMP2.WORDDATE AND SALARYAVG_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 GWHERE U.DEGREE=G.DEGREEAND YEAR(U.STARTWORKDATE)=G.WORDDATEAND (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-语句1UNION ALL-语句2SELECT B.PARENTID,B.ID,B.NAME FROM BBS AS B, TEMP AS T WHERE B.PARENTID=T.ID-语句3)SELECT NAME FROM TEMP;-语句4 运行后