扩展日期范围，从包含排除日期的表中获取一系列日期范围之间的所有日期

Question

我需要获取表格LEAVE每ID的DATE_FROM到DATE_TO之间的所有日期，不包括周末、停工和节假日。考虑到这个记录(ID，DATE_FROM，DATE_TO(：

001   04-OCT-2018   09-OCT-2018
002   05-OCT-2018   05-OCT-2018
...
n     01-OCT-2018   05-OCT-2018

我需要以这种格式获取这些范围之间的所有日期(ID，DAY_TOKEN(：

001  04-OCT-2018
001  05-OCT-2018
001  08-OCT-2018
001  09-OCT-2018
002  05-OCT-2018
...
n    01-OCT-2018
n    02-OCT-2018
n    03-OCT-2018
n    04-OCT-2018
n    05-OCT-2018

我正在使用从我找到的查询修改的查询：

SELECT ID, a.date_from + rnum - 1 AS day_token
FROM (SELECT a.ID, a.date_from, a.date_to, ROWNUM AS rnum
FROM all_objects, leave a
-- Aside from ALL_OBJECT, I cross join it with my LEAVE table
WHERE ROWNUM <= a.date_to - a.date_from + 1) a
WHERE TO_CHAR (a.date_from + rnum - 1, 'DY') NOT IN ('SAT', 'SUN');
AND NOT EXISTS (SELECT 1
FROM holiday b
WHERE b.schedule = d.date_from + rnum - 1)
AND NOT EXISTS (SELECT 1
FROM suspension c
WHERE c.schedule = d.date_from + rnum - 1)

问题是只有第一条记录会正确展开，其他记录不会包含在记录集中，除非DATE_FROM和DATE_TO是同一日期。

我想尽可能避免使用 PL-SQL 函数，但如果不使用函数就无法实现我需要的结果集，请至少告诉我原因。

Answer 1

下面介绍如何为每个 ID 创建与 FROM 和 TO 日期之间天数一样多的行，不包括周末(周六和周日(：

SQL> with leave (id, date_from, date_to) as
2  (select '001', date '2018-10-04', date '2018-10-09' from dual union all
3   select '002', date '2018-10-05', date '2018-10-05' from dual union all
4   select '003', date '2018-10-02', date '2018-10-08' from dual
5  ),
6  inter as
7    (select l.id,
8       l.date_from + column_value datum,
9       to_char(l.date_from + column_value, 'day') day
10     from leave l,
11          table(cast(multiset(select level from dual
12                              connect by level <= l.date_to - l.date_from + 1
13                             ) as sys.odcinumberlist))
14    )
15  select id, datum
16  from inter
17  where to_char(datum, 'dy') not in ('sat', 'sun');
ID  DATUM
--- -----------
001 05-oct-2018
001 08-oct-2018
001 09-oct-2018
001 10-oct-2018
003 03-oct-2018
003 04-oct-2018
003 05-oct-2018
003 08-oct-2018
003 09-oct-2018
9 rows selected.
SQL>

作为第 18 行(依此类推(，添加附加条件(删除假期、暂停等(。

(顺便说一句，我想知道谁和为什么否决你的问题;它的格式很好，显示了你所拥有的，你试图解决它......真的，对我来说是个谜(。