我有一个这样的表结构：
USER_ID START_DATE END_DATE MON TUE WED THU FRI SAT SUN
======= ========== ========== === === === === === === ===
1 2018-03-01 2018-03-15 0 1 0 1 0 0 0
2 2018-02-20 2018-02-23 1 1 1 1 1 0 0
其中 M/T/W 等列是 1/0 以指示事件是否发生在星期一/星期二/星期三等。
所以我需要一个查询，我可以为两个日期之间的 USER_ID 运行该查询，该查询将为该日期范围内的每一天的每次事件返回一行。
因此，例如，如果用户在接下来的 4 周内每周一和周三都有一个事件，那么将有一行以今天作为开始日期，以 4 周时间作为结束日期，其中 MON 和 WED 设置为“1”。
我需要的是一个查询，在这种情况下，如果我在该范围内使用三周的搜索期，将返回 6 行，每行都包含事件发生的日期。
我对如何处理这个完全困惑，任何帮助表示赞赏！
给你（假设日期范围不超过 19 年）：
create table schedule(
user_id int primary key not null,
start_date date not null,
end_date date not null,
mon int not null,
tue int not null,
wed int not null,
thu int not null,
fri int not null,
sat int not null,
sun int not null
);
insert into schedule (user_id, start_date, end_date,
mon, tue, wed, thu, fri, sat, sun)
values (30, '2018-03-01', '2018-03-15', 0, 1, 0, 1, 0, 0, 0);
insert into schedule (user_id, start_date, end_date,
mon, tue, wed, thu, fri, sat, sun)
values (31, '2018-02-20', '2018-02-23', 1, 1, 1, 1, 1, 0, 0);
对于user_id = 30（在 SQL 中输入了 8 次）：
select date_add(start_date, interval 7 * (d2.n * 100 + d1.n * 10 + d0.n) + weekday.d day) as day
from
(select 1 as n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) d2,
(select 1 as n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) d1,
(select 1 as n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) d0,
(select 0 as d from schedule where user_id = 30 and mon = 1
union select 1 as d from schedule where user_id = 30 and tue = 1
union select 2 as d from schedule where user_id = 30 and wed = 1
union select 3 as d from schedule where user_id = 30 and thu = 1
union select 4 as d from schedule where user_id = 30 and fri = 1
union select 5 as d from schedule where user_id = 30 and sat = 1
union select 6 as d from schedule where user_id = 30 and sun = 1) as weekday,
schedule s
where user_id = 30
and date_add(start_date, interval 7 * (d2.n * 100 + d1.n * 10 + d0.n) + weekday.d day) between s.start_date and s.end_date
order by d2.n, d1.n, d0.n
结果：
day
==========
2018-03-02
2018-03-04
2018-03-09
2018-03-11
对于user_id = 31（在 SQL 中输入了 8 次）：
select date_add(start_date, interval 7 * (d2.n * 100 + d1.n * 10 + d0.n) + weekday.d day) as day
from
(select 1 as n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) d2,
(select 1 as n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) d1,
(select 1 as n union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) d0,
(select 0 as d from schedule where user_id = 31 and mon = 1
union select 1 as d from schedule where user_id = 31 and tue = 1
union select 2 as d from schedule where user_id = 31 and wed = 1
union select 3 as d from schedule where user_id = 31 and thu = 1
union select 4 as d from schedule where user_id = 31 and fri = 1
union select 5 as d from schedule where user_id = 31 and sat = 1
union select 6 as d from schedule where user_id = 31 and sun = 1) as weekday,
schedule s
where user_id = 31
and date_add(start_date, interval 7 * (d2.n * 100 + d1.n * 10 + d0.n) + weekday.d day) between s.start_date and s.end_date
order by d2.n, d1.n, d0.n
结果：
day
==========
2018-02-20
2018-02-21
2018-02-22
2018-02-23
十分简单。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句