对于特定用户MySQL,在同一个表中查找日期范围重叠

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了对于特定用户MySQL,在同一个表中查找日期范围重叠，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5750字，纯文字阅读大概需要9分钟。

内容图文

我绝不是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

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

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

所以我要查找的查询(或查询)应返回这些行：

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

这两个答案都非常精彩,任何发现这篇文章的人都有与我相同的问题,请阅读它们并查看小提琴！ :)很多惊人的大脑工作进入他们！暂时我不得不采用我在#1编辑中提到的解决方案但是我将调整我的查询以使用@Ryan Vincent方法@philipxy编辑/评论忽略最初的一天重叠.

解决方法:

这是第一部分：每位用户重叠汽车……

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用户标识和overLap用户标识必须相同
> allCars汽车记录和重叠汽车记录必须不同
> allCars时间范围和重叠时间范围必须重叠.

时间范围检查：

而不是检查重叠时间使用积极测试.最简单的方法是检查它是否重叠,并对其应用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.

内容总结

以上是互联网集市为您收集整理的对于特定用户MySQL,在同一个表中查找日期范围重叠全部内容，希望文章能够帮你解决对于特定用户MySQL,在同一个表中查找日期范围重叠所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端