时间戳

首页 > 解决方案 > 从一组日期生成日期范围的年份

sql - 从一组日期生成日期范围的年份

问题描述

使用 Oracle 12c

我需要从每个 ID 的一组日期中生成多年的日期范围。我尝试使用 LEVEL 和 CONNECT BY 子句，但我不完全理解它，我无法提出解决方案。请参阅下面的示例和预期结果。任何帮助表示赞赏！

WITH dataset AS
   (SELECT 1 id,'01-Jan-2015' dt_fr,'01-Jan-2018' dt_to FROM DUAL
      UNION SELECT 2 id,'01-Dec-2016' dt_fr,'01-Dec-2020' dt_to FROM DUAL
      UNION SELECT 3 id,'01-May-2015' dt_fr,'01-May-2015' dt_to FROM DUAL
      )
   SELECT id
      ,--dt_fr
      ,--dt_to
      FROM dataset

ID  yr_from    yr_to
1   01-Jan-15   31-Dec-15
1   01-Jan-16   31-Dec-16
1   01-Jan-17   31-Dec-17
1   01-Jan-18   31-Dec-18
2   01-Dec-16   30-Nov-17
2   01-Dec-17   30-Nov-18
2   01-Dec-18   30-Nov-19
2   01-Dec-19   30-Nov-20
2   01-Dec-20   30-Nov-21
3   01-May-15   30-Apr-16

标签： sqloracledate-range

解决方案

在 Oracle 12 中，除了connect by，您还可以使用该cross apply子句为每个输入行分别生成“级别”。这使代码更清晰，因为您不必处理数据中的循环，而且速度更快。

在您的代码中，您会显示可能表示日期的字符串。我编写了查询，就好像输入是实际日期（输出也是日期）；我更改了 mynls_date_format以便输出与您的格式匹配，并且我不需要to_date在with子句中明确给出格式。

alter session set nls_date_format = 'dd-Mon-yyyy';

with
  dataset (id, dt_fr, dt_to) as (
    select 1, to_date('01-Jan-2015'), to_date('01-Jan-2018') from dual union all
    select 2, to_date('01-Dec-2016'), to_date('01-Dec-2020') from dual union all
    select 3, to_date('01-May-2015'), to_date('01-May-2015') from dual
  )
select id
     , add_months(dt_fr, 12 * (lvl - 1)) as yr_from
     , add_months(dt_fr, 12 * lvl) - 1   as yr_to
from   dataset
       cross apply
       ( select  level as lvl
         from    dual
         connect by level <= months_between(dt_to, dt_fr) / 12 + 1
       )
order by id, dt_fr   --   if needed
;

 ID YR_FROM     YR_TO      
--- ----------- -----------
  1 01-Jan-2015 31-Dec-2015
  1 01-Jan-2016 31-Dec-2016
  1 01-Jan-2017 31-Dec-2017
  1 01-Jan-2018 31-Dec-2018
  2 01-Dec-2016 30-Nov-2017
  2 01-Dec-2017 30-Nov-2018
  2 01-Dec-2018 30-Nov-2019
  2 01-Dec-2019 30-Nov-2020
  2 01-Dec-2020 30-Nov-2021
  3 01-May-2015 30-Apr-2016

推荐阅读