博客

在shell脚本或者java中可以很简单实现程序循环，但是在sql确不容易。

查阅了大量的资料，整理出一下SQL模板，记录一下。减少日后的工作量

Oracle数据库

SELECT ROWNUM n,ROWNUM*2 n2 ,DATE '2021-06-11'+ROWNUM-1 dt --循环100次
FROM (
	SELECT '2021-06-11' --初始化
	FROM dual 
	WHERE 1=1--终止条件
) tcp
CONNECT BY ROWNUM<=100;

postgres数据库

with recursive temp_table as (
    select
    current_date::timestamp + '10 days' sub_date
    union
    select sub_date + '-1 day' sub_date_1
    from temp_table 
    where sub_date > '2019-01-01'::timestamp
)/*,
calendar_table as (
select 
sub_date
from temp_table
where  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