Z}C_(K0
7i7y fRH4G0ORACLE中大家可能对递归(在oracle中很多人称作家族树)已经很熟悉,因为ORACLE中的递归比较简单,也容易掌握!刚接触DB2的时候,也碰到过递归的问题,因为研究的人少所以刚接触的时候,对db2有一种神秘的感觉,db2是很强大的这个误用质疑!就sql而言,凡是oracle用sql能实现的,db2肯定或多或少也可以实现,只是逻辑复杂点!因为一直有人提问这边的问题,所以今天抽时间写了一点ITPUB个人空间{DH%v3Dlo

JC xo E0首先,关于db2递归的一点基础知识:
!V1}@yu8?Q h0
b&[6k)H Gp0db2中的递归查询使用with来实现,也称为公共表达式,公共表达式在select语句的开始部分采用with子句的形式,在使用公共表达式的查询中可以多次使用它,并且公共表达式还可以通过取别名来连接到他本身,这样的话就可以达到循环的目的。ITPUB个人空间5\$R h6} S[$rB

gs \ @zZJ0递归查询通常有3个部分需要定义:ITPUB个人空间8]*uNW g^
ITPUB个人空间7p j.U6f7?&}(Z*_2V ?8}
一:一个公共表达式形式的虚拟表。
4F5J$D7G(y8~0二:一个初始化表。ITPUB个人空间 \7v/|,u)\g!ewQP
三:一个与虚拟表进行完全内连接的辅助表。
d:u9Ms.pm3B@G`0
1Q{X6H"P,\+l0需要使用UNION all合并上边3个查询,然后用select从递归输出中得到最终的结果。
1Np-bX0xe0
;Y?(} T3D]i.f0大体上如下形式ITPUB个人空间5lX)aNQ t1s
with XX(x1,x2,x3) as -------@0
6UJ'kd R+Vtb0(
R ]3ff&md q.up;t0select a.s,a.s1 from a ----@1ITPUB个人空间? \'ex!b8^ Af
union all ----@2ITPUB个人空间&v3ht$x"a~7b7Z1I Kv
select * from a,xx where a.s=xx.x1 ------@3ITPUB个人空间w'}K0FtwD"]
)
|V XK#{L8qF W(y`0select ... from xx where .... -------@4
6F/K#Y$U^`.Y0ITPUB个人空间t$O ahp
@0:为with体,即虚拟表ITPUB个人空间I\q_Y0Q(C3{ |"M,V
@1:为初始化表,这里需要定义初始化的一些行,也就是你递归的出发点,或者说父行,这部分逻辑只执行一次,它的结果作为虚拟表递归的初始化内容。ITPUB个人空间0rwJA@#`
@2:这里必须用UNION all
]-wB {#y1yp0@3:这里需要定义递归的条件(辅助表),这里定义递归的逻辑,需要注意的是父行和子行进行连接的时候逻辑一定要清楚父子关系,不然很容易变成死循环的,这里首先将初始化表的结果作为条件进行查询,在把执行的结果添加到虚拟表中,只要这里能查询出来记录,那么就会进行下一步递归循环。
:\an+`8P!@'d0p0@4:这里就是对虚拟表的查询语句。
_$`R~K!|r9W0ITPUB个人空间 qJf-E#b(K
关于关于递归更多的知识,可以到google中找更专业的资料,这里只讲我理解的一些东西。
-`Sl3R3U;z$]DI;]0这里举一个简单点的例子:
2f6OO.om;L0
pNVye7SLb h s0-------------------------------ITPUB个人空间.Ak-gO!hA/O.f T:z
Q:求一SQL--关于数据纵横转换的ITPUB个人空间n8|%I h%JR3e.M

e8d1f~)mUh0ITPUB个人空间Id;]kZ1P[
假设表A中有以下数据ITPUB个人空间ru H-|%Y,D"TH B
A1 1 01
l5_?x z _ D0A1 1 02
~&JT)_$Z}/C-r{Ps0A1 1 03
w!J1]#Mu0A2 1 01
`B[8@ bNF0A2 2 01
"z`PDv-l/W3^{M0A2 2 02ITPUB个人空间s| { ~;QV
现希望一SQL能够查出下边这样的数据(暂时假设表A中第三列只有01-03这三种可能值)ITPUB个人空间oX8w2Rv6bX)o
A1 1 01-02-03
)io8~6smEX0A2 1 01ITPUB个人空间5uNt,@$O:f N r!r p
A2 2 01-02
\{/Fzf4P0ITPUB个人空间h'X3D H/S
A:这个查询牵扯到全表的递归循环,用一般的sql语句很难搞定!ITPUB个人空间Fm)dVM
ITPUB个人空间&g:u9Z]C8yf!A
这是我刚写的一个递归语句!分享给大家看看!
K l fd#z[J0ITPUB个人空间7a!V6M)D,rQi.Pg
因为懒,所以把建立测试表的逻辑也略去了,这里用with来生成上边的测试数据。
)r T Iq|-n@ [0with t (t1,t2,t3) as (
M$xH/n3wp I(DK0values
b5f,Ha)[4F,C.o*t8R0('A1', 1 ,'01'),('A1' ,1, '02'),('A1', 1, '03'),
(d)il;|-X0('A2', 1, '01'),('A2', 2, '01'),('A2', 2 ,'02')ITPUB个人空间XMd.c"D:GZIE2K?
)ITPUB个人空间3d$O`:fO}s$e3['C7T
select * from t;ITPUB个人空间3S(I2}0^rI9H1g#?
测试一下:ITPUB个人空间m&I2Mt&C
ITPUB个人空间!yNy;z(A;{/x
C:\>db2 connect to dwITPUB个人空间iZES)`Ol:s
ITPUB个人空间k$Oj;~] H3kD
数据库连接信息
E@*yz mu)k4N]0ITPUB个人空间kZ3s3H2c5R z xg
数据库服务器 = DB2/NT 8.2.0
{B x X_!r$Q \0SQL 授权标识 = DB2ADMINITPUB个人空间+MoM1lUY
本地数据库别名 = DW
NTH-x(@!bw0
6Cu'e.z oU7y"Z#z0
3Cejab f0C:\>db2 with t (t1,t2,t3) as ( \
1f"LT }Pk[0db2 (cont.) => values \
4qT1A#uWT3w"Y/?0db2 (cont.) => ('A1', 1 ,'01'),('A1' ,1, '02'),('A1', 1, '03'), \ITPUB个人空间(tmzH7}[MC7c S t
db2 (cont.) => ('A2', 1, '01'),('A2', 2, '01'),('A2', 2 ,'02') \ITPUB个人空间k b${3P/J2K
db2 (cont.) => ) \
n+mU%b#Uk2JpB9t \e0db2 (cont.) => select * from t
^b Du/Tm0
y4Qs.ZH*p-JAK5|c0T1 T2 T3
e`!sR s0-- ----------- --
WGS IVs0A1 1 01ITPUB个人空间 g+e%{[;?ab D3h+b P
A1 1 02
*lg.r:Py4S*BRC0A1 1 03
]Eu*D*~!F(B/n3O+i5oO/J0A2 1 01ITPUB个人空间t7l3m"D+i
A2 2 01
s wx,rNU0A2 2 02
kd#c)tR*v0ITPUB个人空间!b8tq3}2Zu4VB
6 条记录已选择。
Y0YxnJK/D0b l0ITPUB个人空间Lwb?r H Rh
ITPUB个人空间 jX.a"MY _MsQ
现在公布递归逻辑,sql如下:
TH?)oFE%x0with t (t1,t2,t3) as (
'^;y+DMKj?,]0valuesITPUB个人空间b [ m?E
('A1', 1 ,'01'),('A1' ,1, '02'),('A1', 1, '03'),ITPUB个人空间y/?-r$b o+O'K'c`3V
('A2', 1, '01'),('A2', 2, '01'),('A2', 2 ,'02')
8P)wWcP C%qvM0),
]Jouf q0t1(t11,t22,t33,t44,t55) as (
v,S T,Z}sI0select t1,t2,t3,rownumber() over(partition by t1,t2),rownumber() over(partition by t1,t2) as t4 from t
e;m&z^;c\ z0),ITPUB个人空间B:}2A2WF [J+nA1B
t3(s1,s2,s3,s4,s5) as(
,ilj!m'S-r^0u L\0select t11,t22,cast(t33 as varchar(100)),t44,t55 from t1 where T44 =1 and t55=1ITPUB个人空间#ir,l q Q7d&d(D
union all
7VYD,qJ/O&b IE c0select a.s1,a.s2,cast(a.s3||'-'||b.t33 as varchar(100)),a.s4+1,a.s5 from t3 a,t1 bITPUB个人空间-l ? rS)H9V?
where a.s1=b.t11 and a.s4 = b.t55-1)
(aU2h7d_$?0select s1,s2,s3 from t3 x where x.s4=(select max(s4) from t3 y where x.s1=y.s1) order by s1,s2
5TN2|)n @gI0;
/r(k8F'jg"^4Ec0ITPUB个人空间8\/n6ou QzX
测试一下:
,r$_)kI9A5["Lcu0
X2_[3P!Kt0ITPUB个人空间-dGc^JG P_F
C:\>db2 connect to dw
O$F$WO5r-z`0
;fnq3{,C0 数据库连接信息ITPUB个人空间A S4mhOp

#v6`|&woF4f0数据库服务器 = DB2/NT 8.2.0ITPUB个人空间T pI Cg2Xh
SQL 授权标识 = DB2ADMIN
\OU _f R0v0本地数据库别名 = DWITPUB个人空间UX"gnxY
ITPUB个人空间]2x r"yh

SH3A$O;}|%]0C:\>db2 with t (t1,t2,t3) as ( \ITPUB个人空间5Tp WrRm:yF%j
db2 (cont.) => values \
9x)D^0} ]z7hE0db2 (cont.) => ('A1', 1 ,'01'),('A1' ,1, '02'),('A1', 1, '03'), \
[Ar S{ g~0db2 (cont.) => ('A2', 1, '01'),('A2', 2, '01'),('A2', 2 ,'02') \ITPUB个人空间;?Yz-]9Z {u f+K @r O^
db2 (cont.) => ), \ITPUB个人空间 \*[*M1a{ d6ivd
db2 (cont.) => t1(t11,t22,t33,t44,t55) as ( \
Um6BVi I6C6ZN0db2 (cont.) => select t1,t2,t3,rownumber() over(partition by t1,t2),rownumber() over(partition by t1,t2) as t4 from t \ITPUB个人空间F'ja{ h+Q?
db2 (cont.) => ), \ITPUB个人空间zW9sz.c0b@ N
db2 (cont.) => t3(s1,s2,s3,s4,s5) as( \ITPUB个人空间e3n'W kj&si?
db2 (cont.) => select t11,t22,cast(t33 as varchar(100)),t44,t55 from t1 where T44 =1 and t55=1 \ITPUB个人空间JmH8oL,H7pU
db2 (cont.) => union all \
W1yx([4t M_Peq0db2 (cont.) => select a.s1,a.s2,cast(a.s3||'-'||b.t33 as varchar(100)),a.s4+1,a.s5 from t3 a,t1 b \
k0hvb9lJ;o i7~;|0db2 (cont.) => where a.s1=b.t11 and a.s4 = b.t55-1) \
Qa]s3PHQe`0db2 (cont.) => select s1,s2,s3 from t3 x where x.s4=(select max(s4) from t3 y where x.s1=y.s1) order by s1,s2
7OiN"lJgc0
%T![/s+@5G.N9esC0S1 S2 S3
8uQqj8kM5M$J0-- ----------- ----------------------------------------------------------------------------------------------------ITPUB个人空间$]!L} ^Q3^
SQL0347W 递归公共表表达式 "DB2ADMIN.T3" 可能包含无限循环。 SQLSTATE=01605ITPUB个人空间HI^|+g$x6OFPHoX

J2_^Na0A1 1 01-02-03
B,L },Px0AMq)o0A2 1 01-02ITPUB个人空间VNtJ3ig0`7B
A2 2 01-02
4S${#{:SJk0
)}/tO\6Yg0 已选择 3 条记录,打印 1 条警告消息。
Sd2F@Y9c,{,s0ITPUB个人空间'hr!I&~/b1z9N

4_|:B;_ O{0ok,搞定!ITPUB个人空间~.@N|0PLEzmy d
ITPUB个人空间L9a.ZX0]JwX