mysql - 为特定用户 MySQL 在同一个表中查找日期范围重叠

Question

我绝不是 MySQL 专家，所以我正在寻求有关此事的任何帮助。

我需要执行一个简单的测试(原则上)，我有这个(简化的)表:

tableid | userid  | car      | From        | To
--------------------------------------------------------
1       | 1       |  Fiesta  |  2015-01-01 | 2015-01-31
2       | 1       |  MX5     |  2015-02-01 | 2015-02-28
3       | 1       |  Navara  |  2015-03-01 | 2015-03-31
4       | 1       |  GTR     |  2015-03-28 | 2015-04-30
5       | 2       |  Focus   |  2015-01-01 | 2015-01-31
6       | 2       |  i5      |  2015-02-01 | 2015-02-28
7       | 2       |  Aygo    |  2015-03-01 | 2015-03-31
8       | 2       |  206     |  2015-03-29 | 2015-04-30
9       | 1       |  Skyline |  2015-04-29 | 2015-05-31
10      | 2       |  Skyline |  2015-04-29 | 2015-05-31

我需要在这里找到两件事:

1. 如果任何用户在他的汽车分配中有超过一天的日期重叠(分配的结束可以与新分配的开始在同一天)。
2. 是否有任何两个用户试图在同一日期分配同一辆车，或者他们在同一辆车上的日期范围重叠。

所以我正在寻找的查询(或多个查询)应该返回那些行:

tableid | userid  | car      | From        | To
--------------------------------------------------------
3       | 1       |  Navara  |  2015-03-01 | 2015-03-31
4       | 1       |  GTR     |  2015-03-28 | 2015-04-30
7       | 2       |  Aygo    |  2015-03-01 | 2015-03-31
8       | 2       |  206     |  2015-03-29 | 2015-04-30
9       | 1       |  Skyline |  2015-04-29 | 2015-05-31
10      | 2       |  Skyline |  2015-04-29 | 2015-05-31 

我觉得我在用头撞墙，我很高兴能够在单独的查询中进行这些比较。我需要将它们显示在一个表中，但我总是可以加入结果。

我已经完成了研究和几个小时的测试，但我离我想要的结果还很远。

SQLFiddle with the above test data

顺便说一句，我试过这些帖子(它们不是我所需要的，但已经足够接近了，或者我是这么认为的):

Comparing two date ranges within the same table

How to compare values of text columns from the same table

这是我能找到的最接近的解决方案，但是当我在单个表上尝试它(将表连接到自身)时，我得到了疯狂的结果:Checking a table for time overlap?

编辑

作为临时解决方案，我采用了一种不同的方法，类似于我在研究期间发现的帖子(上文)。我现在将检查新车租赁/分配日期是否与表格中的任何日期范围重叠。如果是这样，我将保存与日期重叠的行的 ID。这样至少我将能够标记重叠并允许用户查看标记的行并手动解决任何重叠。

感谢所有为此提供帮助的人，除非有人有更好的方法来实现这一点，否则我会将 philipxy 答案标记为已选答案(在接下来的 24 小时内)。我毫不怀疑，按照他的回答，我最终将能够达到我需要的结果。目前我需要采用任何有效的解决方案，因为我需要在接下来的几天内完成我的项目，因此改变了方法。

编辑 #2

这两个答案都很棒，对于任何发现这篇文章和我有同样问题的人，请阅读它们并查看 fiddle ! :) 他们付出了很多惊人的脑力劳动!暂时我不得不采用我在#1 编辑中提到的解决方案，但我将调整我的查询以配合@Ryan Vincent 方法 + @philipxy 编辑/评论关于忽略最初的一天重叠。

Answer 1

这是第一部分:每个用户重叠的汽车...

SQLFiddle - correlated Query and Join Query

第二部分-一辆车同时有多个用户:SQLFiddle - correlated Query and Join Query .下面查询...

我使用相关查询:

您可能需要 userid 和 'car' 的索引。但是 - 请检查“解释计划”以查看 mysql 如何访问数据。试试吧:)

每个用户重叠的汽车

查询:

SELECT `allCars`.`userid`  AS `allCars_userid`, 
       `allCars`.`car`     AS `allCars_car`, 
       `allCars`.`From`    AS `allCars_From`, 
       `allCars`.`To`      AS `allCars_To`,
       `allCars`.`tableid` AS `allCars_id`
 FROM  
       `cars` AS `allCars`
 WHERE 
     EXISTS  
         (SELECT 1       
          FROM `cars` AS `overlapCar`            
          WHERE 
               `allCars`.`userid` = `overlapCar`.`userid` 
           AND `allCars`.`tableid` <> `overlapCar`.`tableid`          
           AND NOT (   `allCars`.`From`  >= `overlapCar`.`To`      /* starts after outer ends  */  
                    OR `allCars`.`To`    <= `overlapCar`.`From`))  /* ends before outer starts */
 ORDER BY
        `allCars`.`userid`, 
        `allCars`.`From`, 
        `allCars`.`car`;      

结果:

allCars_userid  allCars_car  allCars_From  allCars_To  allCars_id  
--------------  -----------  ------------  ----------  ------------
             1  Navara       2015-03-01    2015-03-31             3
             1  GTR          2015-03-28    2015-04-30             4
             1  Skyline      2015-04-29    2015-05-31             9
             2  Aygo         2015-03-01    2015-03-31             7
             2  206          2015-03-29    2015-04-30             8
             2  Skyline      2015-04-29    2015-05-31            10

为什么有效？或者我是怎么想的:

我使用相关查询，所以我不需要处理重复项，这对我来说可能是最容易理解的。还有其他表达查询的方式。每个都有优点和缺点。我想要一些我可以轻松理解的东西。

要求:对于每个用户，确保他们不会同时拥有两辆或更多汽车。

因此，对于每个用户记录 (AllCars)，请检查整个表 (overlapCar)，看看您是否可以找到与当前记录时间重叠的不同记录。如果找到一个，则选择我们正在检查的当前记录(在 allCars 中)。

因此重叠检查是:

• allCars userid 和overLap userid 必须相同

• allCars 汽车记录和overlap 汽车记录必须不同

• allCars 时间范围和overLap 时间范围必须重叠。

  时间范围检查:

  不要检查重叠时间，而是使用阳性测试。最简单的方法是检查它是否重叠，然后对其应用 NOT。

一辆车同时有多个用户...

查询:

SELECT  `allCars`.`car`     AS `allCars_car`,
        `allCars`.`userid`  AS `allCars_userid`,  
        `allCars`.`From`    AS `allCars_From`, 
        `allCars`.`To`      AS `allCars_To`, 
        `allCars`.`tableid` AS `allCars_id`
        
 FROM  
       `cars` AS `allCars`
 WHERE 
     EXISTS  
        (SELECT 1       
         FROM `cars` AS `overlapUser`            
         WHERE 
              `allCars`.`car` = `overlapUser`.`car` 
          AND `allCars`.`tableid` <> `overlapUser`.`tableid`          
          AND NOT (    `allCars`.`From`  >= `overlapUser`.`To`       /* starts after outer ends  */  
                   OR  `allCars`.`To`    <= `overlapUser`.`From`))  /* ends before outer starts */
 ORDER BY
        `allCars`.`car`,      
        `allCars`.`userid`, 
        `allCars`.`From`;

 

结果:

allCars_car  allCars_userid  allCars_From  allCars_To    allCars_id  
-----------  --------------  ------------  ----------  ------------
Skyline                   1  2015-04-29    2015-05-31             9
Skyline                   2  2015-04-29    2015-05-31            10

编辑:

鉴于@philipxy 的评论，关于需要“大于或等于”检查的时间范围，我已经更新了此处的代码。我没有更改 SQLFiddles。