db2的递归:3个小例子,前两个在网上看到的,后面一个自己在工作中遇到的,和大家分享下
--1.一个简单的例子
with t(id) as
(
select 1 as id from sysibm.sysdummy1 a
union all
select id+1 as id from t,sysibm.sysdummy1 a where id<=99
)
select id from t;
结果为:
1
2
3
···
100
--2.另一个稍微复杂点的
with n(str, ori, pos) as (
values ('AA BB CC DD ', 1, posstr('AA BB CC DD ', ' '))
union all
select str, pos+1, locate(' ', str, pos+1) from n where locate(' ', str, pos+1)>0
)
select str, ori, pos, substr(str, ori, pos-ori) as result from n;
--分析
str , ori, pos
'AA BB CC DD ', 1 , 3
'AA BB CC DD ', 4 , 6