MySQL查询根据日期范围重叠选择不同的行 [英] MySQL query to select distinct rows based on date range overlapping

问题描述

假设我们有一个表(table1)，其中存储了4个值(user_id，name，start_date，end_date)

Let's say we have a table (table1) in which we store 4 values (user_id, name, start_date, end_date)

 table1
 ------------------------------------------------
 id   user_id   name     start_date    end_date
 ------------------------------------------------
 1      1       john     2016-04-02    2016-04-03
 2      2       steve    2016-04-06    2016-04-06
 3      3       sarah    2016-04-03    2016-04-03
 4      1       john     2016-04-12    2016-04-15

然后输入开始日期为2016-04-03和结束日期为2016-04-03，以查看是否有任何用户可以预定工作.检查并忽略重叠日期的查询将返回以下内容:

I then enter a start_date of 2016-04-03 and end_date of 2016-04-03 to see if any of the users are available to be scheduled for a job. The query that checks for and ignores overlapping dates returns the following:

table1
------------------------------------------------
id   user_id   name     start_date    end_date
------------------------------------------------
2      2       steve    2016-04-06    2016-04-06
4      1       john     2016-04-12    2016-04-15

我遇到的问题是，即使约翰已被预订一份我要寻找的日期的工作，但他仍会显示在列表中.该查询为其他条目返回TRUE，因为日期没有冲突，但是我想从列表中完全隐藏John，因为他将不可用.

The issue I am having is that John is being displayed on the list even though he is already booked for a job for the dates I am searching for. The query returns TRUE for the other entry because the dates don't conflict, but i would like to hide John from the list completely since he will be unavailable.

如果输入的日期与同一用户的另一个条目相冲突，是否可以过滤列表并阻止显示用户信息?

Is there a way to filter the list and prevent the user info from displaying if the dates entered conflict with another entry for the same user?

查询示例:

SELECT DISTINCT id, user_id, name, start_date, end_date 
FROM table1 
WHERE ('{$startDate}' NOT BETWEEN start_date AND end_date 
AND '{$endDate}' NOT BETWEEN start_date AND end_date  
AND start_date NOT BETWEEN '{$startDate}' AND '{$endDate}' 
AND end_date NOT BETWEEN '{$startDate}' AND '{$endDate}');

推荐答案

问题中的解决方案"看起来根本不正确.

The "solution" in the question doesn't look right at all.

INSERT INTO table1 VALUES  (5,2,'steve', '2016-04-01','2016-04-04')

现在，史蒂夫(Steve)有一个重叠的行.

Now there's a row with Steve having an overlap.

该问题中作为SOLUTION提出的查询将返回'steve'.

And the query proposed as a SOLUTION in the question will return 'steve'.

这里展示的是构建查询以返回请求的时间段内可用"用户的示例，因为表1中没有该用户的行与请求的时间段重叠".

Here's a demonstration of building a query to return the users that are "available" during the requested period, because there is no row in table1 for that user that "overlaps" with the requested period.

第一个问题是，由于存在与请求的时间段重叠的行，因此使不的用户不可用.假设表中所有行的开始日期< =结束日期...

First problem is getting the users that are not available due to the existence of a row that overlaps the requested period. Assuming that start_date <= end_date for all rows in the table...

如果行的end_date在请求的时间段的开始日期或之后，且行的start_date在请求的时间段的开始日期或之前，则行与请求的时间段重叠>所请求时段的版本.

A row overlaps the requested period, if the end_date of the row is on or after the start of the requested period, and the start_date of the row is on or before the ed of the requested period.

-- users that are "unavailable" due to row with overlap
SELECT t.user_id
  FROM table1 t
 WHERE t.end_date   >= '2016-04-03'  -- start of requested period
   AND t.start_date <= '2016-04-03'  -- end of requested_period
 GROUP
    BY t.user_id   

(如果我们不假设start_date< = end_date不成立，则可以在查询中将该检查添加为条件)

(If our assumption that start_date <= end_date doesn't hold, we can add that check as a condition in the query)

要获取所有用户的列表，我们可以查询包含不同用户列表的表.我们没有在问题中看到类似的表格，因此我们可以获取出现在table1中的所有用户的列表

To get a list of all users, we could query a table that has a distinct list of users. We don't see a table like that in the question, so we can get a list of all users that appear in table1 instead

SELECT l.user_id
  FROM table1 l
 GROUP BY l.user_id

要获取所有用户列表(不包括不可用的用户)，我们可以通过以下两种方法进行编写.最简单的是反连接模式:

To get the list of all users excluding the users that are unavailable, there are couple of ways we can write that. The simplest is an anti-join pattern:

SELECT a.user_id
  FROM ( -- list of all users 
         SELECT l.user_id
           FROM table1 l
          GROUP BY l.user_id
       ) a
  LEFT
  JOIN ( -- users that are unavailable due to overlap
         SELECT t.user_id
           FROM table1 t
          WHERE t.end_date   >= '2016-04-03'  -- start of requested period
            AND t.start_date <= '2016-04-03'  -- end of requested_period
          GROUP
             BY t.user_id
       ) u
    ON u.user_id = a.user_id
 WHERE u.user_id IS NULL

这篇关于MySQL查询根据日期范围重叠选择不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！