从开始日期到结束日期每周显示计划器事件

u3r8eeie 于 2021-06-17 发布在 Mysql

关注(0)|答案(3)|浏览(251)

我已经抓挠了我的头在过去的两天对这个-我需要一个Maven的眼睛！
基本上，我试图显示一个星期内，直到他们关闭的司机计划事件。如果司机从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\u0\u到\u999999表。我使用的是mariadb的最新版本。
sql小提琴链接
如果安装了mariadb，可以在本地计算机上复制上面的表和sql语句。
基本上，在代码中，如果驱动程序在某一天关闭，我将使用in\ u array函数从可用性框中删除它们。我试图通过1个查询来实现这一点，在这里它检索一周中的空日或司机休息的地方。目前，只有在给定日期有条目的情况下，查询才起作用，问题是，如果结束日期还没有结束，查询就不会结转。

下面是一个执行当前查询的好例子。如果你看下面的屏幕，马里奥在2019年1月1日之前是休息的，但是第19、20、21、22和23天是空白的，不要说出他的名字。他们可能需要一个交叉连接的地方-逻辑思维！

任何帮助将不胜感激，并感谢您采取暂停！

sql mysql Database mariadb

来源：https://stackoverflow.com/questions/53748383/display-planner-events-each-week-from-start-date-until-end-date

3条答案

按热度按时间

oxalkeyp1#

我发现你的问题有点难以理解。但我想你想要的是：

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可以被替换为一个计算。
这将为您提供与所需日期范围重叠的事件的所有驱动程序的列表。如果你想要的是所有司机谁没有一个事件在该日期范围内，然后只要扭转日期测试。
当然，你应该列出你真正感兴趣的领域，而不是“选择*”。我只是在那里偷懒。
更新
啊，我明白了。你需要的是每天有空的人的名单，而不仅仅是一周中某个时间有空的人。
好吧，据我所知，要做到这一点，你需要一张每天都有记录的table。有多种方法可以创建这样的表，我不知道如何使用标准sql。如果创建那个表是我们可以讨论的问题，但是让我假设它不是，并且这样一个表是存在的，被称为“workdays”，并且每个记录都包含一个日期字段“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

赞(0）回复(0）举报 2021-06-18

7eumitmz2#

有位先生叫安德烈(https://dba.stackexchange.com/users/6965/andriy-m)他过去帮助过我，他回答了我的期望。感谢@jay为这个问题发布了另一个答案。
下面是正确的sql查询：

SELECT sub.planner_start_date, COALESCE(d.driver_name, 'N/A') AS driver_name, e.planner_id, e.event_name, e.event_start_date, e.event_start_time, e.event_end_date, e.event_end_time
FROM
( SELECT
        '2018-12-30' + INTERVAL seq.seq DAY AS planner_start_date
     FROM
        seq_0_to_999999 AS seq
     WHERE
        seq.seq <= TIMESTAMPDIFF(DAY, '2018-12-30', '2019-01-05')
    ) as sub
LEFT JOIN planner_events AS e ON e.event_start_date <= sub.planner_start_date AND e.event_end_date >= sub.planner_start_date
LEFT JOIN drivers AS d ON e.user_id = d.driver_id AND e.user_type = 'driver'

谢谢大家的帮助。

赞(0）回复(0）举报 2021-06-18

t9aqgxwy3#

在我看来，这就是你所需要的全部问题：

SELECT d.driver_id
     , d.driver_name
     , e.planner_id
     , e.event_name
     , e.event_start_date
     , e.event_end_date
  FROM drivers d 
  JOIN planner_events e 
    ON e.user_id = d.driver_id;

其他的一切看起来都是一个显示问题-最好在应用程序代码中解决。

赞(0）回复(0）举报 2021-06-18

我来回答

从开始日期到结束日期每周显示计划器事件

3条答案

相关问题

热门标签

最新问答