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博客 ” ，链接：https://blog.itpub.net/11419868/viewspace-1013865/ ，如需转载，请注明出处，否则将追究法律责任。
