代码之家 › 专栏 › 技术社区 › lmocsi

从SQL中的日期列获取日期范围

range oracle sql

lmocsi · 技术社区 · 4 年前

我有一个带有日期列(date_for)的表,我想获取该列的连续日期范围。

select
  date_for
from (select to_date('20210910','yyyymmdd') date_for from dual union all
      select to_date('20210911','yyyymmdd') date_for from dual union all
      select to_date('20210912','yyyymmdd') date_for from dual union all
      select to_date('20210913','yyyymmdd') date_for from dual union all
      select to_date('20210915','yyyymmdd') date_for from dual union all
      select to_date('20210916','yyyymmdd') date_for from dual union all
      select to_date('20210917','yyyymmdd') date_for from dual union all
      select to_date('20211011','yyyymmdd') date_for from dual union all
      select to_date('20211012','yyyymmdd') date_for from dual union all
      select to_date('20211108','yyyymmdd') date_for from dual
      )

所需的输出类似于:

2021.09.10-2021.09.13
2021.09.15-2021.09.17
2021.10.11-2021.10.12
2021.11.08-2021.11.08

我该如何查询?

0 回复 | 直到 4 年前

Littlefoot 4 年前

你必须隔离岛屿例如

SQL> WITH
  2     test
  3     AS
  4        (SELECT TO_DATE ('20210910', 'yyyymmdd') date_for FROM DUAL
  5         UNION ALL
  6         SELECT TO_DATE ('20210911', 'yyyymmdd') date_for FROM DUAL
  7         UNION ALL
  8         SELECT TO_DATE ('20210912', 'yyyymmdd') date_for FROM DUAL
  9         UNION ALL
 10         SELECT TO_DATE ('20210913', 'yyyymmdd') date_for FROM DUAL
 11         UNION ALL
 12         SELECT TO_DATE ('20210915', 'yyyymmdd') date_for FROM DUAL
 13         UNION ALL
 14         SELECT TO_DATE ('20210916', 'yyyymmdd') date_for FROM DUAL
 15         UNION ALL
 16         SELECT TO_DATE ('20210917', 'yyyymmdd') date_for FROM DUAL
 17         UNION ALL
 18         SELECT TO_DATE ('20211011', 'yyyymmdd') date_for FROM DUAL
 19         UNION ALL
 20         SELECT TO_DATE ('20211012', 'yyyymmdd') date_for FROM DUAL
 21         UNION ALL
 22         SELECT TO_DATE ('20211108', 'yyyymmdd') date_for FROM DUAL)
 23    SELECT MIN (date_for) fie, MAX (date_for) lie
 24      FROM (SELECT date_for,
 25                     TO_NUMBER (TO_CHAR (date_for, 'yyyymmdd'))
 26                   - ROW_NUMBER () OVER (ORDER BY date_for) diff
 27              FROM test)
 28  GROUP BY diff
 29  ORDER BY 1;

FIE        LIE
---------- ----------
2021.09.10 2021.09.13
2021.09.15 2021.09.17
2021.10.11 2021.10.12
2021.11.08 2021.11.08

SQL>

在上查看更多选项 Finding islands 在OraFAQ上。

lmocsi 4 年前

一个更简单的解决方案:

select
   min(date_for) st,
   max(date_for) en
from
(select
   date_for,
   date_for-rownum x
 from (select to_date('20210910','yyyymmdd') date_for from dual union all
       select to_date('20210911','yyyymmdd') date_for from dual union all
       select to_date('20210912','yyyymmdd') date_for from dual union all
       select to_date('20210913','yyyymmdd') date_for from dual union all
       select to_date('20210915','yyyymmdd') date_for from dual union all
       select to_date('20210916','yyyymmdd') date_for from dual union all
       select to_date('20210917','yyyymmdd') date_for from dual union all
       select to_date('20211011','yyyymmdd') date_for from dual union all
       select to_date('20211012','yyyymmdd') date_for from dual union all
       select to_date('20211108','yyyymmdd') date_for from dual
       )
 )
group by x
order by 1