MySQL选择每天日期范围内的行

Question

我有一个包含类似以下范围的事件的表：

id | title | start      | end
1  | Lorem | 2019-11-02 | 2019-11-03
2  | Ipsum | 2019-11-02 | 2019-11-02
3  | Dolor | 2019-11-08 | 2019-11-10
4  | Amet  | 2019-11-02 | 2019-11-04

我希望可以选择所有行，但需要将日期从区间中加入，这样每个事件每天就可以有X行结果。结果应该来自于我的示例表格：

date        | id | title | start      | end
2019-11-02  | 1  | Lorem | 2019-11-02 | 2019-11-03
2019-11-02  | 2  | Ipsum | 2019-11-02 | 2019-11-02
2019-11-02  | 4  | Amet  | 2019-11-02 | 2019-11-04
2019-11-03  | 1  | Lorem | 2019-11-02 | 2019-11-03
2019-11-03  | 4  | Amet  | 2019-11-02 | 2019-11-04
2019-11-04  | 4  | Amet  | 2019-11-02 | 2019-11-04
2019-11-08  | 3  | Dolor | 2019-11-08 | 2019-11-10
2019-11-09  | 3  | Dolor | 2019-11-08 | 2019-11-10
2019-11-10  | 3  | Dolor | 2019-11-08 | 2019-11-10

我真的卡住了，不知道是否可能...谢谢你的帮助！

我正在使用MySQL 5.7。

- Mushr00m

---

考虑处理演示层/应用程序级别代码中的数据显示问题，假设您已经拥有了这个（例如，一个简单的PHP循环作用于一个有序数组）。 - Strawberry

Answer 1

如果您正在运行MySQL 8.0，这是一个简单的递归查询：

with recursive cte as (
    select start as date, id, title, start, end from mytable
    union all
    select date + interval 1 day, id, title, start, end from cte where date < end
)
select * from cte
order by date, id

DB Fiddle演示:

日期      | 编号 | 标题   | 开始时间    | 结束时间    
:--------- | -: | :---- | :--------- | :---------
2019-11-02 |  1 | Lorem | 2019-11-02 | 2019-11-03
2019-11-02 |  2 | Ipsum | 2019-11-02 | 2019-11-02
2019-11-02 |  4 | Amet  | 2019-11-02 | 2019-11-04
2019-11-03 |  1 | Lorem | 2019-11-02 | 2019-11-03
2019-11-03 |  4 | Amet  | 2019-11-02 | 2019-11-04
2019-11-04 |  4 | Amet  | 2019-11-02 | 2019-11-04
2019-11-05 |  3 | Dolor | 2019-11-05 | 2019-11-08
2019-11-06 |  3 | Dolor | 2019-11-05 | 2019-11-08
2019-11-07 |  3 | Dolor | 2019-11-05 | 2019-11-08
2019-11-08 |  3 | Dolor | 2019-11-05 | 2019-11-08

在早期版本中，典型解决方案包括数字表。 以下是一种解决方案，可处理长达4天的时间跨度（您可以扩展子查询以获取更多）：

select
    t.start + interval x.n day date,
    t.*
from 
mytable t
inner join (
    select 0 n union all select 1 union all select 2 union all select 3 union all select 4
) x on t.start + interval x.n day <= t.end
order by date, id

在DB Fiddle上的演示

- GMB

Answer 2

尝试使用一个带有每天日期字段的日历表。 这样，您可以在日期字段上对日历表进行左连接，如下所示：

SELECT 
calendar_table.date_field,
my_table.date,
my_table.id,
my_table.title,
my_table.start,
my_table.end
FROM calendar_table
LEFT JOIN my_table ON my_table.date = calendar_table.date_field

- unit303