如何根据系统日期获取过去一年的不同日期范围

Question

我有一个场景：根据我的系统日期，我需要捕获过去 12 个月的日期。

示例：我将获得一个数字参数，例如：2,3,4,6

。如果参数为 3：则根据 sysdate-12，我预计会有 4 条记录，如下所示

Start_Date  End_Date
20180801    20181101
20181101    20190201
20190201    20190501
20190501    20190827
select TO_CHAR(add_months(trunc(sysdate, 'month'), -12),'YYYYMMDD') Start_Date,TO_CHAR(add_months(trunc(sysdate, 'month'), -9),'YYYYMMDD') End_Date from dual 
union all 
select TO_CHAR(add_months(trunc(sysdate, 'month'), -9),'YYYYMMDD') Start_Date,TO_CHAR(add_months(trunc(sysdate, 'month'), -6),'YYYYMMDD') End_Date from dual 
union all 
select TO_CHAR(add_months(trunc(sysdate, 'month'), -6),'YYYYMMDD') Start_Date,TO_CHAR(add_months(trunc(sysdate, 'month'), -3),'YYYYMMDD') End_Date from dual 
union all 
select TO_CHAR(add_months(trunc(sysdate, 'month'), -3),'YYYYMMDD') Start_Date,TO_CHAR(trunc(sysdate),'YYYYMMDD') End_Date from dual

在两个日期之间，我相差 3 个月。如果参数为 2，则 Start_Date 和 End_Date 之间的差异应该是 2 个月，这意味着我将获得 6 条记录。

我们可以编写一个查询来读取这个数字参数并在参数的基础上创建记录吗？不用像上面这样写多个查询，我有没有可能读取参数并创建记录

Answer 1

您可以使用以下查询：

SELECT
ADD_MONTHS(START_,(LEVEL - 1) * &&INPUT_NUMBER) AS START_DATE,
CASE
WHEN LEVEL = 12 / ( &&INPUT_NUMBER ) THEN SYSDATE
ELSE ADD_MONTHS(START_,(LEVEL) * &&INPUT_NUMBER)
END AS END_DATE
FROM
(
SELECT
ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), - 12) START_
FROM
DUAL
)
CONNECT BY
LEVEL <= 12 / ( &&INPUT_NUMBER );

-- 输入参数为 2

SQL> SELECT
2      ADD_MONTHS(START_,(LEVEL - 1) * &&INPUT_NUMBER) AS START_DATE,
3      CASE
4          WHEN LEVEL = 12 / ( &&INPUT_NUMBER ) THEN SYSDATE
5          ELSE ADD_MONTHS(START_,(LEVEL) * &&INPUT_NUMBER)
6      END AS END_DATE
7  FROM
8      (
9          SELECT
10              ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), - 12) START_
11          FROM
12              DUAL
13      )
14  CONNECT BY
15      LEVEL <= 12 / ( &&INPUT_NUMBER )
16  ;

START_DAT END_DATE
--------- ---------
01-AUG-18 01-OCT-18
01-OCT-18 01-DEC-18
01-DEC-18 01-FEB-19
01-FEB-19 01-APR-19
01-APR-19 01-JUN-19
01-JUN-19 27-AUG-19
6 rows selected.
SQL>

-- 输入参数为 3

SQL> SELECT
2      ADD_MONTHS(START_,(LEVEL - 1) * &&INPUT_NUMBER) AS START_DATE,
3      CASE
4          WHEN LEVEL = 12 / ( &&INPUT_NUMBER ) THEN SYSDATE
5          ELSE ADD_MONTHS(START_,(LEVEL) * &&INPUT_NUMBER)
6      END AS END_DATE
7  FROM
8      (
9          SELECT
10              ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), - 12) START_
11          FROM
12              DUAL
13      )
14  CONNECT BY
15      LEVEL <= 12 / ( &&INPUT_NUMBER )
16  ;
Enter value for input_number: 3

START_DAT END_DATE
--------- ---------
01-AUG-18 01-NOV-18
01-NOV-18 01-FEB-19
01-FEB-19 01-MAY-19
01-MAY-19 27-AUG-19
SQL>

干杯！！

Answer 2

与@Tejash基本相同的思想，使用分层查询，但使用绑定变量而不是替换变量，并向后计数而不是向前计数：

var your_var number;
exec :your_var := 3;
select
add_months(trunc(sysdate, 'MM'), -(level) * :your_var) as period_start,
case when level = 1 then trunc(sysdate, 'DD' )
else add_months(trunc(sysdate, 'MM'), -(level - 1) * :your_var)
end as period_end
from dual
connect by level <= 12/:your_var
order by period_start;
PERIOD_STA PERIOD_END
---------- ----------
2018-08-01 2018-11-01
2018-11-01 2019-02-01
2019-02-01 2019-05-01
2019-05-01 2019-08-27

exec :your_var := 2;
...
PERIOD_STA PERIOD_END
---------- ----------
2018-08-01 2018-10-01
2018-10-01 2018-12-01
2018-12-01 2019-02-01
2019-02-01 2019-04-01
2019-04-01 2019-06-01
2019-06-01 2019-08-27

但你也可以使用递归子查询分解来做到这一点：

exec :your_var := 3;
with rcte (period_start, period_end, final_end) as (
select add_months(trunc(sysdate, 'MM'), -:your_var),
trunc(sysdate, 'DD'),
add_months(trunc(sysdate, 'MM'), -12)
from dual
union all
select add_months(period_start, -:your_var),
add_months(trunc(period_end, 'MM'), -:your_var),
final_end
from rcte
where period_start > final_end
)
select period_start, period_end
from rcte
order by period_start;
PERIOD_STA PERIOD_END
---------- ----------
2018-08-01 2018-11-01
2018-11-01 2019-02-01
2019-02-01 2019-05-01
2019-05-01 2019-08-27

exec :your_var := 2;
...
PERIOD_STA PERIOD_END
---------- ----------
2018-08-01 2018-10-01
2018-10-01 2018-12-01
2018-12-01 2019-02-01
2019-02-01 2019-04-01
2019-04-01 2019-06-01
2019-06-01 2019-08-27

或间隔：

with rcte (period_start, period_end, final_end) as (
select trunc(sysdate, 'MM') -:your_var * interval '1' month,
trunc(sysdate, 'DD'),
trunc(sysdate, 'MM') - interval '1' year
from dual
union all
select period_start -:your_var * interval '1' month,
trunc(period_end, 'MM') -:your_var * interval '1' month,
final_end
from rcte
where period_start > final_end
)
select period_start, period_end
from rcte
order by period_start;