英文:
How to query all the dates between 2023-07-03 and 2023-08-06 (5 weeks from the start date)?
问题
以下是翻译好的部分：
以下数据库已创建：
`INSERT INTO T4S_BRANCH_DATA (BR_NAME, BR_START, BR_COURSE_LN) VALUES ('COMMUNITY',
TO_DATE('2023-07-03', 'YYYY-MM-DD'), 5);
INSERT INTO T4S_BRANCH_DATA (BR_NAME, BR_START, BR_COURSE_LN) VALUES ('HAF', TO_DATE('2023-07-
03', 'YYYY-MM-DD'), 4);
INSERT INTO T4S_BRANCH_DATA (BR_NAME, BR_START, BR_COURSE_LN) VALUES ('HUBB', TO_DATE('2023-
07-03', 'YYYY-MM-DD'), 4);
查询尝试：
`select BR_START + level - 1 DT
from t4s_branch_data where BR_NAME = 'COMMUNITY'
connect by level <= (
(BR_START + BR_COURSE_LN*7-1) - BR_START + 1);
但是，这会打印出 5,000 行重复的数据。
英文:
Below database created:
`INSERT INTO T4S_BRANCH_DATA (BR_NAME, BR_START, BR_COURSE_LN) VALUES ('COMMUNITY',
TO_DATE('2023-07-03', 'YYYY-MM-DD'), 5);
INSERT INTO T4S_BRANCH_DATA (BR_NAME, BR_START, BR_COURSE_LN) VALUES ('HAF', TO_DATE('2023-07-
03', 'YYYY-MM-DD'), 4);
INSERT INTO T4S_BRANCH_DATA (BR_NAME, BR_START, BR_COURSE_LN) VALUES ('HUBB', TO_DATE('2023-
07-03', 'YYYY-MM-DD'), 4);
Query tried:
`select BR_START + level - 1 DT
from t4s_branch_data where BR_NAME = 'COMMUNITY'
connect by level <= (
(BR_START + BR_COURSE_LN*7-1) - BR_START + 1);
However, this prints 5,000 rows of repeated data
答案1
得分: 1
在应用CONNECT BY子句之前，您需要限制要使用的行：
with comm as
(select * from t4s_branch_data where BR_NAME = 'COMMUNITY')
select BR_START + level - 1 DT
from comm
connect by level <= (
(BR_START + BR_COURSE_LN*7-1) - BR_START + 1);
或者，可以使用内联视图代替WITH子句。
英文:
you need to limit the rows you want to use BEFORE you apply the connect by clause:
with comm as
(select * from t4s_branch_data where BR_NAME = 'COMMUNITY')
select BR_START + level - 1 DT
from comm
connect by level <= (
(BR_START + BR_COURSE_LN*7-1) - BR_START + 1);
or use an inline view instead of a WITH clause
答案2
得分: 0
在连接子查询中有（超过足够的）级别的一个选项，限制在Where子句中生成的日期数量：
SELECT t.BR_START + l.LVL "DT"
FROM t4s_branch_data t
INNER JOIN ( SELECT LEVEL - 1 "LVL" From Dual Connect By LEVEL <= 60 ) l ON(1 = 1)
WHERE t.BR_NAME = 'COMMUNITY' And l.LVL <= ( (t.BR_START + t.BR_COURSE_LN*7-1) - t.BR_START )
英文:
One of the options is to have (more than enough) Levels in joined subquery limiting the number of dates generated in Where clause:
SELECT t.BR_START + l.LVL "DT"
FROM t4s_branch_data t
INNER JOIN ( SELECT LEVEL - 1 "LVL" From Dual Connect By LEVEL <= 60 ) l ON(1 = 1)
WHERE t.BR_NAME = 'COMMUNITY' And l.LVL <= ( (t.BR_START + t.BR_COURSE_LN*7-1) - t.BR_START )
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论