connect by的妙用

connect by的妙用

1 构造任意个数的集合(比如100个)

select rownum rn from dual connect by rownum <=100

2 构造全年日期

select to_date('2005-01-01','yyyy-mm-dd')+rownum-1

from dual

connect by rownum<=365

[@more@]

db2中有无类似的函数呢,可也用如下变通:

1

with temptb(level) as
(
select 1 level
from sysibm.sysdummy1
union all
select b.level+1
from temptb b
)
select * from temptb
fetch first 100 rows only ;

2(这个语句需要源表的行数大于366,其实可以用上面的方法替换)

select date('2005-01-01')+(rn -1 ) days
from (
SELECT row_number() over() as rn
FROM SYSIBM.SYSCOLUMNS a
--fetch first 366 rows only
) a
where rn<=365;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11419868/viewspace-1013865/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11419868/viewspace-1013865/