mysql - 从开始日期到结束日期每周显示计划程序事件

Question

在过去的 2 天里，我一直在为这个问题挠头 - 我需要专家的眼光!

基本上，我试图在 1 周内为司机显示计划程序事件，直到他们下类。如果司机在 2018 年 12 月 10 日至 2018 年 12 月 21 日期间下类，那么我希望司机延期到第二天并显示他/她的名字直到结束日期。现在，结果仅显示当天的驱动程序，而不是每天重复出现。

这是一个示例数据集:

CREATE TABLE IF NOT EXISTS planner_events (
  planner_id INT(5) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_name VARCHAR(100) NOT NULL,     
  event_start_date DATE,    
  event_end_date DATE,
  user_id INT(5) NOT NULL,  
  user_type VARCHAR(10) NOT NULL,
  date_created DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO planner_events (event_name, event_start_date, event_end_date, user_id, user_type)
VALUES ('Annual Holiday', '2018-12-12', '2018-12-16', 101, 'driver'), 
('Alex on leave', '2018-12-12', '2018-12-15', 102, 'driver'), 
('Reminder', '2018-12-13', '2018-12-13', 103, 'driver');

CREATE TABLE IF NOT EXISTS drivers (
  driver_id INT(5) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  driver_name VARCHAR(100) NOT NULL, 
  date_created DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO drivers (driver_id, driver_name)
VALUES (101, 'Alex'), (102, 'Tom'),(103, 'Trevor');

我创建了一个 SQL Fiddle，但默认情况下 SQL Fiddle 没有存储 seq_0_to_999999 表。我正在使用最新版本的 MariaDB。

SQL Fiddle Link

如果您安装了 MariaDB，您可以将上面的表和 SQL 语句复制到您的本地计算机上。

基本上，在代码中，如果驱动程序在给定的一天关闭，我将使用 in_array 函数从可用性框中删除驱动程序。我试图通过 1 个查询来实现这一点，它检索一周的空天或司机关闭的地方。目前，查询仅在有给定日期的条目时才有效，问题是，如果结束日期尚未结束，则查询不会继续。

这是执行当前查询的一个很好的例子。如果您查看下面的屏幕，马里奥将在 2019 年 1 月 1 日之前休息，但第 19、20、21、22 和 23 天是空白的，并且没有说出他的名字。他们可能需要在某处进行 CROSS JOIN - 逻辑思考!

如有任何帮助，我们将不胜感激，感谢您抽出宝贵的时间!

Answer 1

我发现您的 fiddle 中的查询有点难以理解。但我认为你想要的是:

select *
from planner_events e
join drivers d on d.driver_id=e.user_id
where e.event_start <= @enddate and e.event_end >= @startdate
  and e.user_type='driver'

@startdate 是您感兴趣的时间段的第一天，@enddate 是最后一天。如果范围的大小始终相同，一周或其他时间，@enddate 可以替换为计算。

这应该会为您提供事件与所需日期范围重叠的所有司机的列表。如果您想要的是所有在该日期范围内没有赛事的车手，那么只需反转日期测试即可。

哦，当然你应该列出你真正感兴趣的任何字段而不是“select *”。我只是在那里偷懒。

更新

啊，我明白了。您需要每天有空的人员列表，而不仅仅是一周中某个时间有空的人员。

好的，据我所知，要做到这一点，您需要一个包含每天记录的表格。有多种方法可以创建这样的表，我不知道使用标准 SQL 的方法。如果创建该表是我们可以解决的问题，但让我假设它不是，并且这样的表存在并且称为“工作日”并且每个记录都包含一个日期字段“date1”。

然后查询可以这样完成:

select w.date1, d.driver_id, d.driver_name
from workdays w
cross join drivers d
where w.date1 between @start and @end
 and not exists (select * from planner_event 
   where event_start<=w.date1 and event_end>=w.date1 and user_id=d.driver_id and user_type='driver')
order by w.date1, d.driver_id

这会给出一个包含日期和可用驱动程序列表的结果集，然后您必须使用代码将其格式化为您想要的表格。如果您正在寻找结果集，每天有一列，每个司机一行，或者类似的东西，我相信我们可以做到这一点，必须多尝试一下。我认为这将是一个困惑的查询，每天都有一个子查询。

更新 2

啊。如果我们知道周期是一周，那么我们就不需要工作日表。我们可以这样做:

select d.driver_id, d.driver_name,
  case when exists (select * from planner_events where '2018-12-31' between event_start_date and event_end_date and user_id=d.driver_id) then 'N' else 'Y' end as Monday,
  case when exists (select * from planner_events where DATE_ADD('2018-12-31', INTERVAL 1 DAY) between event_start_date and event_end_date and user_id=d.driver_id) then 'N' else 'Y' end as Tuesday,
  case when exists (select * from planner_events where DATE_ADD('2018-12-31', INTERVAL 2 DAY) between event_start_date and event_end_date and user_id=d.driver_id) then 'N' else 'Y' end as Wednesday,
  case when exists (select * from planner_events where DATE_ADD('2018-12-31', INTERVAL 3 DAY) between event_start_date and event_end_date and user_id=d.driver_id) then 'N' else 'Y' end as Thursday,
  case when exists (select * from planner_events where DATE_ADD('2018-12-31', INTERVAL 4 DAY) between event_start_date and event_end_date and user_id=d.driver_id) then 'N' else 'Y' end as Friday
from drivers d
order by d.driver_id