db2 java调用公共表达式,DB2 公共表表达式（WITH语句的使用）

说起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、以学历和入职年份为条件 联合两个结果集，查找工资

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

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

查询结果完全正确，但我们还有改善的空间，在查询结果集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

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

可能有些朋友会说，我不用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)

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)

那使用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