sql - 如何从双重中选择日期，但要加入数据？

Question

我遇到了一个我无法解决的 SQL 问题。

首先，一个 SQL 摆弄它:http://sqlfiddle.com/#!4/fe7b07/2

如您所见，我在表格中填写了一些日期，这些日期绑定(bind)到了某个 ID。那些日子一天天过去。所以对于这个例子，如果我们只看一月，我们就会有这样的东西:



从 2020-01-01 到 2020-01-31 的时间线， block 是数据库中的日期。所以这将是简单的 SELECT * FROM days输出。

我现在想要的是在这个输出中填写几天。这些将跨越 timeline_begin至MIN(date_from) ;来自 MAX(date_from)至timeline_end .

我将在下图中将这些标记为红色:



也不需要添加橙色跨度，但如果您的解决方案也可以这样做，那也可以。

好的，到目前为止一切顺利。

为此，我创建了 SELECT * FROM minmax , 这将选择 MIN(date_from)和 MAX(date_from)对于每个 id_othertable .仍然没有涉及魔法。

我现在正在努力为每个 id_othertable 创造那些日子，同时也加入他们拥有的数据(在这个 fiddle 中，它只是 some_info 字段)。

我试着把这个写在 SELECT * FROM days_before查询，但我就是无法让它工作。我读到了神奇的功能CONNECT BY ，它将自己逐行创建日期，但我无法从前一个表中加入我的数据。每次我加入信息时，每个 id_othertable 只能得到一行，不是我需要的所有日期。

所以我正在寻找的理想解决方案是拥有三个选择查询:

SELECT * FROM days从数据库中选择日期

SELECT * FROM days_before这将显示 MIN(date_from) 之前的日期查询 1

SELECT * FROM days_after MAX(date_from) 之后的日期查询 1

最后我会 UNION这三个查询将它们全部组合在一起。

我希望我能很好地解释我的问题。如果您需要任何信息或进一步解释，请随时询问。

编辑 1:我用一些示例数据创建了一个 pastebin: https://pastebin.com/jskrStpZ

请记住，只有第一个查询具有来自数据库的实际信息，其他两个已创建数据。此外，此示例输出仅包含 id_othertable = 1 的数据。 ，所以实际查询也应该有 id_othertable = 2, 3的信息.

编辑 2:只是为了澄清，字段 date_to只是一个简单的 date_from + 1 day .

Answer 1

如果您对日期进行了非规范化，这很简单:

with bas as (
    select 1 id_other_table, to_date('2020-01-05', 'YYYY-MM-DD') date_from, to_date('2020-01-06', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-06', 'YYYY-MM-DD') date_from, to_date('2020-01-07', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-07', 'YYYY-MM-DD') date_from, to_date('2020-01-08', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-10', 'YYYY-MM-DD') date_from, to_date('2020-01-11', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-11', 'YYYY-MM-DD') date_from, to_date('2020-01-12', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 1 id_other_table, to_date('2020-01-12', 'YYYY-MM-DD') date_from, to_date('2020-01-13', 'YYYY-MM-DD') date_to, 'hello' some_info from dual
    union all select 2 id_other_table, to_date('2020-01-10', 'YYYY-MM-DD') date_from, to_date('2020-01-11', 'YYYY-MM-DD') date_to, 'my' some_info from dual
    union all select 2 id_other_table, to_date('2020-01-11', 'YYYY-MM-DD') date_from, to_date('2020-01-12', 'YYYY-MM-DD') date_to, 'my' some_info from dual
    union all select 2 id_other_table, to_date('2020-01-12', 'YYYY-MM-DD') date_from, to_date('2020-01-13', 'YYYY-MM-DD') date_to, 'my' some_info from dual
    union all select 3 id_other_table, to_date('2020-01-20', 'YYYY-MM-DD') date_from, to_date('2020-01-21', 'YYYY-MM-DD') date_to, 'friend' some_info from dual
    union all select 3 id_other_table, to_date('2020-01-21', 'YYYY-MM-DD') date_from, to_date('2020-01-22', 'YYYY-MM-DD') date_to, 'friend' some_info from dual
    union all select 3 id_other_table, to_date('2020-01-22', 'YYYY-MM-DD') date_from, to_date('2020-01-23', 'YYYY-MM-DD') date_to, 'friend' some_info from dual)
, ad as (select trunc(sysdate,'YYYY') -1 + level all_dates from dual connect by level <= 31)
select distinct some_info,all_dates from bas,ad where (some_info,all_dates) not in (select some_info,date_from from bas)

如果您有更长的日期范围或时间，查询需要另一个解决方案是有帮助的。但这更难调试。因为橙色时段很难拿到