英文:
Expand the date and insert into another table
问题
需要扩展表格的第二行部分，并插入到名为fct的表格中。
表格1：- 部分
表格2：- fct
不依赖于A列，而B列在查询中将以类似于'％MT'的形式出现。
部分
a b c d e f g
600 MT_000 6765 29-04-22 30-04-22 1 WEEK
MT 6758 01-05-22 31-05-22 1 MONTH
601 MT_0001 6771 01-06-22 30-06-22 1 WEEK
想要的样子
FCT
a b c d e f g
600 MT_000 6765 30-04-22 30-04-22 1 WEEK
MT 6758 01-05-22 07-05-22 1 MONTH
MT 6758 08-05-22 14-05-22 1 MONTH
MT 6758 15-05-22 21-05-22 1 MONTH
MT 6758 22-05-22 28-05-22 1 MONTH
MT 6758 29-05-22 31-05-22 1 MONTH
MT 6758 01-06-22 02-06-22 1 WEEK
英文:
Need to expand this second row of table part
and insert into table fct table.
Table 1:- Part
Table 2:- fct
do not depend on A column and B column will in a form in query is b like '%MT'
Part
a b c d e f g
600 MT_000 6765 29-04-22 30-04-22 1 WEEK
MT 6758 01-05-22 31-05-22 1 MONTH
601 MT_0001 6771 01-06-22 30-06-22 1 WEEK
want like this
FCT
a b c d e f g
600 MT_000 6765 30-04-22 30-04-22 1 WEEK
MT 6758 01-05-22 07-05-22 1 MONTH
MT 6758 08-05-22 14-05-22 1 MONTH
MT 6758 15-05-22 21-05-22 1 MONTH
MT 6758 22-05-22 28-05-22 1 MONTH
MT 6758 29-05-22 31-05-22 1 MONTH
MT 6758 01-06-22 02-06-22 1 WEEK
答案1
得分: 0
你可以像这样应用递归子查询来生成以月份为单位的行：
select a, b, c, case g when 'MONTH' then d1 else d end d, least(d2, e) e, f, g
from fct
cross apply (
select trunc(d, 'mm') + (level - 1) * 7 d1, trunc(d, 'mm') + level * 7 - 1 d2
from dual
connect by g = 'MONTH' and trunc(d, 'mm') + (level - 1) * 7 <= e )
英文:
You can apply recursive subquery producing weeks to month-type rows like here:
select a, b, c, case g when 'MONTH' then d1 else d end d, least(d2, e) e, f, g
from fct
cross apply (
select trunc(d, 'mm') + (level - 1) * 7 d1, trunc(d, 'mm') + level * 7 - 1 d2
from dual
connect by g = 'MONTH' and trunc(d, 'mm') + (level - 1) * 7 <= e )
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论