在shell脚本或者java中可以很简单实现程序循环,但是在sql确不容易。
查阅了大量的资料,整理出一下SQL模板,记录一下。减少日后的工作量
Oracle数据库
SELECT ROWNUM n,ROWNUM*2 n2 ,DATE '-06-11'+ROWNUM-1 dt --循环100次FROM (SELECT '-06-11' --初始化FROM dual WHERE 1=1--终止条件) tcpCONNECT BY ROWNUM<=100;
postgres数据库
with recursive temp_table as (selectcurrent_date::timestamp + '10 days' sub_dateunionselect sub_date + '-1 day' sub_date_1from temp_table where sub_date > '-01-01'::timestamp)/*,calendar_table as (select sub_datefrom temp_tablewhere EXTRACT(ISODOW FROM sub_date) not in (6, 7)and sub_date not in (select non_work_date from report.world_date where type = '2' and country_id = 'CHINA')union select non_work_date from report.world_date where type = '1' and country_id = 'CHINA'order by sub_date )--排除节假日*/select * from temp_table