关于db2递归的一个例子

wangzhonnew

等我升了VP有资格拉人的时候一定优先考虑你

askgyliu

This is a book I would think the best book to learn DB2 SQL:

http://mysite.verizon.net/Graeme_Birchall/id1.html

Look for "Recursive SQL"

myfriend2010

这个好，

原帖由 askgyliu 于 2008-3-14 20:06 发表
This is a book I would think the best book to learn DB2 SQL:

http://mysite.verizon.net/Graeme_Birchall/id1.html

Look for "Recursive SQL"

cuidch

with t (t1,t2,t3) as (
values
('A1', 1 ,'01'),('A1' ,1, '02'),('A1', 1, '03'),
('A2', 1, '01'),('A2', 2, '01'),('A2', 2 ,'02')
),
t1(t11,t22,t33,t44) as (
select t1,t2,t3,rownumber() over(partition by t1,t2)  from t
),
t3(s1,s2,s3,s4) as(
select t11,t22,cast(t33 as varchar(100)),t44 from t1 where T44 =1
union all
select a.s1,a.s2,cast(a.s3||'-'||b.t33 as varchar(100)),a.s4+1 from t3 a,t1 b where a.s1=b.t11 and a.s2=b.t22  and a.s4 = b.t44-1
)
select s1,s2,s3 from t3 x where x.s4=(select max(s4) from t3 y where x.s1=y.s1 and x.s2=y.s2) order by s1,s2 ;


这样写也是可以的
不知道楼主多加一列的目的是什么？请教一下

sheisqueen

DB2 9.5里也支持 Connect By了。

pastime_Wang

不错,父、子表的关系讲的很详细;

其实应该注意到
db2的递归结果是水平的,也就是一个level接着一个level的找;

oracle则是垂直的,从一个节点向下直到遍历到叶子节点;

2者各有利弊,看具体的业务查询要求了,呵呵

不对的地方请指正


另外：

t1(t11,t22,t33,t44,t55) as (
select t1,t2,t3,rownumber() over(partition by t1,t2) as t4,rownumber() over(partition by t1,t2) as t5 from t

我增加"t4\t5"列的别名,看上去更舒服一些;：）

jully_20081020

厉害！！！

huajieaixue

很强大~~~

calmfeeling

留个爪，慢慢看

jhe_001

up