英文:
In oracle SQL am trying to print dates in one column which is between two date column
问题
下面是一个例子，我有类似的数据，我希望在两个日期字段之间连接日期。
表 ABC
| ID | 开始日期 | 结束日期 |
|---|---|---|
| 1 | 12/03/2021 | 22/05/2021 |
| 1 | 05/06/2022 | 15/10/2022 |
| 2 | 01/01/2023 | 18/04/2023 |
| 3 | 29/03/2020 | 06/06/2020 |
| 3 | 31/05/2023 | 11/08/2023 |
| 3 | 12/12/2022 | 20/03/2023 |
我希望获取开始日期和结束日期之间的日期，并将其在结果列中以分号分隔打印出来。输出应该类似于下面的样子。
| ID | 开始日期 | 结束日期 | 结果 |
|---|---|---|---|
| 1 | 12/03/2021 | 22/05/2021 | 01/04/2021; 01/09/2022 |
| 1 | 05/06/2022 | 15/10/2022 | 01/07/2022; 01/08/2022 |
| 2 | 01/01/2023 | 18/04/2023 | 01/02/2023; 01/03/2023 |
| 3 | 29/03/2020 | 06/06/2020 | 01/04/2020; 01/05/2020 |
| 3 | 31/05/2023 | 11/08/2023 | 01/06/2023; 01/07/2023 |
| 3 | 12/12/2022 | 20/03/2023 | 01/01/2022; 01/02/2023 |
提前感谢…
英文:
Below is an example, I have a similar data and I am looking to concat dates between two date fields
Table ABC
| ID | From | To |
|---|---|---|
| 1 | 12/03/2021 | 22/05/2021 |
| 1 | 05/06/2022 | 15/10/2022 |
| 2 | 01/01/2023 | 18/04/2023 |
| 3 | 29/03/2020 | 06/06/2020 |
| 3 | 31/05/2023 | 11/08/2023 |
| 3 | 12/12/2022 | 20/03/2023 |
Am looking to get dates between From and To column and print it in Results column with separated by ;
The output should look something similar like below.
| ID | From | To | Result |
|---|---|---|---|
| 1 | 12/03/2021 | 22/05/2021 | 01/04/2021; 01/09/2022 |
| 1 | 05/06/2022 | 15/10/2022 | 01/07/2022; 01/08/2022 |
| 2 | 01/01/2023 | 18/04/2023 | 01/02/2023; 01/03/2023 |
| 3 | 29/03/2020 | 06/06/2020 | 01/04/2020; 01/05/2020 |
| 3 | 31/05/2023 | 11/08/2023 | 01/06/2023; 01/07/2023 |
| 3 | 12/12/2022 | 20/03/2023 | 01/01/2022; 01/02/2023 |
Thanks in advance...
答案1
得分: 0
使用递归和函数add_months()以及listagg()：
select id, dt1, dt2,
(select listagg(add_months(trunc(dt1, 'month'), level),';;')
within group (order by level) from dual
connect by add_months(trunc(dt1, 'month'), level) < dt2 ) dates
from abc
英文:
Use recursion and functions add_months() and listagg():
select id, dt1, dt2,
(select listagg(add_months(trunc(dt1, 'month'), level),';')
within group (order by level) from dual
connect by add_months(trunc(dt1, 'month'), level) < dt2 ) dates
from abc
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论