从两个日期列生成日期范围 [英] Generate date range from two date columns

问题描述

下面的源表:

Id   start_date   end_date    field1
1    01/03/2019   07/03/2019  text1
2    10/04/2019   15/04/2019  text2

我想得到这个输出:

Id date        field1
1  01/03/2019  text1
1  02/03/2019  text1
1  03/03/2019  text1
1  04/03/2019  text1
1  05/03/2019  text1
1  06/03/2019  text1
1  07/04/2019  text1
2  10/04/2019  text2
2  11/04/2019  text2
2  12/04/2019  text2
2  13/04/2019  text2
2  14/04/2019  text2
2  15/04/2019  text2

我必须使用循环来填充此表吗?

I must use a loop to populate this table ?

谢谢

推荐答案

您可以使用

You may use a connect by query using the prior + sys_guid() trick

select id,start_date + level - 1 as "date", field1 from t 
connect by level <= end_date - start_date  + 1
    and prior id = id
and prior sys_guid() is not null;

演示

这篇关于从两个日期列生成日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！