如何从双重日期中选择日期,但要合并数据?

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

First of all, an SQL fiddle with it: http://sqlfiddle.com/#!4/fe7b07/2

如您所见,我在表中填充了一些日期,这些日期与某些ID绑定在一起。这些日期每天都在。因此,对于此示例,如果仅看一月,我们将具有以下内容:

I should have been an painter, not database admin

The timelines spanning from 2020-01-01 to 2020-01-31, the blocks are the dates in the database. So this would be the simple SELECT * FROM days output.

What I now want is to fill in some days to this output. These would span from timeline_begin to MIN(date_from); and from MAX(date_from) to timeline_end.

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

enter image description here

也不必添加橙色范围,但是如果您的解决方案也可以这样做,那也可以。

好的,到目前为止很好。

For this I created the SELECT * FROM minmax, which will select the MIN(date_from) and MAX(date_from) for every id_othertable. Still no magic involved.

What I struggle is now creating those days for every id_othertable, while also joining the data they have on them (in this fiddle, it's just the some_info field).

I tried to write this in the SELECT * FROM days_before query, but I just can't get it to work. I read about the magical function CONNECT BY, which will on its own create dates line by line, but I can't get to join my data from the former table. Every time I join the info, I only get one line per id_othertable, not all those dates I need.

因此,我正在寻找的理想解决方案是具有三个选择查询:

  1. SELECT * FROM days which select dates out of the database
  2. SELECT * FROM days_before which will show the dates before MIN(date_from) of query 1
  3. SELECT * FROM days_after for dates after MAX(date_from) of query 1

And in the end I'd UNION those three queries to have them all combined.

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

评论