查找特定用户MySQL的同一个表中的日期范围重叠 [英] Find date range overlaps within the same table, for specific user MySQL

问题描述

我不是一个MySQL专家，所以我正在寻找这方面的任何帮助。

我需要执行一个简单的测试（原则上）我有这个（简化）表：

  tableid | userid |汽车|从|至
 --------------------------------------------- ----------- 
 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 |焦点| 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 |天际线2015-04-29 | 2015-05-31 
 10 | 2 |天际线2015-04-29 | 2015-05-31 
  

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

1. 如果任何用户在他的汽车分配中具有超过一天的日期重叠（分配的结束可以与新分配开始的同一天）。


2. 有任何两个用户尝试获取在同一日期分配的同一辆汽车，或者同一辆汽车上的日期范围重叠。

所以我查找的查询应该返回这些行：

  tableid | userid |汽车|从|至
 --------------------------------------------- ----------- 
 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 |天际线2015-04-29 | 2015-05-31 
 10 | 2 |天际线2015-04-29 | 2015-05-31 
  

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

我做了研究和几个小时的测试，但我不能在结果附近我想。

SQLFiddle上面的测试数据

我试过这些帖子btw（他们不是我需要的，但足够接近，所以我想）：

比较两个日期范围同一个表

如何比较同一表中的文本列的值

这是我可以找到的最接近的解决方案但是当我尝试在一个单一的表（加入表本身）我得到疯狂的结果：检查表格的时间是否重叠？

EDIT

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

感谢提供他们的帮助的每个人与此，我将标志philipxy答案作为选择一个（在接下来的24小时），除非有人有更好的方式实现这一点。我毫不怀疑，在他的答复后，我将能够最终达到我需要的结果。目前，我需要采用任何解决方案，因为我需要在接下来的几天完成我的项目，因此改变方法。

编辑＃ 2

这两个答案都很棒，对于发现这篇文章有同样问题的人，请阅读这两篇文章， :)很多惊人的大脑工作进入他们！暂时我不得不使用我在＃1编辑的解决方案，但我会适应我的查询去@Ryan Vincent方法+ @philipxy编辑/评论忽略初始的一天重叠。

解决方案

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

SQLFiddle - 相关查询和联接查询

第二部分 - 同一时间在一个汽车中有多个用户： SQLFiddle - 相关查询和连接查询。查询以下...

我使用相关的查询：

您可能需要索引userid和'汽车'。但是 - 请检查解释计划，看看mysql如何访问数据。只需尝试：）

每个用户重叠车辆

查询：

  SELECT`allCars`.`userid` AS`allCars_userid`，
`allCars``` 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 
从`cars` AS`overlapCar`
 WHERE 
`allCars`````userid` =`overlappingCar`.`userid` 
和`allCars`.`tableable`<>`overlappingCar`.`tabletable` 
 AND NOT（` allCars`.`From`> =`overlappingCar`.`To` / *在外部结束后启动* / 
或`allCars`.`To`< =`overlappingCar`.`From`））/ *在外部启动之前结束* / 
 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 userid 和 overLap userid 必须相同


• allCars 汽车记录和重叠汽车记录必须不同



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

  
  
  

  时间范围检查：

  
  
  

  而不是检查重叠时间，使用正测试。最简单的方法是检查它不重叠，并应用 NOT 。

一辆同时拥有多个用户的汽车...

查询： / p>

  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 
从`cars` AS`overlappedUser`
 WHERE 
`allCars`.`car` =`overlappedUser`.`car` 
和`allCars`.`tableable`<>`overlappingUser`.`tabletable` 
 AND NOT（`allCars`.`From`> =`overlappedUser`.`To` / *在外部结束后启动* / 
或`allCars`.`To`< =`overlappingUser`.`From`） ）/ * end 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 。

I am by no means an MySQL expert, so I am looking for any help on this matter.

I need to perform a simple test (in principle), I have this (simplified) table:

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

I need to find two things here:

1. If any user has date overlaps in his car assignments of more than one day (end of the assignment can be on the same day as the new assignment start).
2. Did any two users tried to get the same car assigned on the same date, or the date ranges overlap for them on the same car.

So the query (or queries) I am looking for should return those rows:

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 

I feel like I am bashing my head against the wall here, I would be happy with being able to do these comparisons in separate queries. I need to display them in one table but I could always then join the results.

I've done research and few hours of testing but I cant get nowhere near the result I want.

SQLFiddle with the above test data

I've tried these posts btw (they were not exactly what I needed but were close enough, or so I thought):

Comparing two date ranges within the same table

How to compare values of text columns from the same table

This was the closest solution I could find but when I tried it on a single table (joining table to itself) I was getting crazy results: Checking a table for time overlap?

EDIT

As a temporary solution I have adapted a different approach, similar to the posts I have found during my research (above). I will now check if the new car rental / assignment date overlaps with any date range within the table. If so I will save the id(s) of the rows that the date overlaps with. This way at least I will be able to flag overlaps and allow a user to look at the flagged rows and to resolve any overlaps manually.

Thanks to everyone who offered their help with this, I will flag philipxy answer as the chosen one (in next 24h) unless someone has better way of achieving this. I have no doubt that following his answer I will be able to eventually reach the results I need. At the moment though I need to adopt any solution that works as I need to finish my project in next few days, hence the change of approach.

Edit #2

The both answers are brilliant and to anyone who finds this post having the same issue as I did, read them both and look at the fiddles! :) A lot of amazing brain-work went into them! Temporarily I had to go with the solution I mention in #1 Edit of mine but I will be adapting my queries to go with @Ryan Vincent approach + @philipxy edits/comments about ignoring the initial one day overlap.

解决方案

Here is the first part: Overlapping cars per user...

SQLFiddle - correlated Query and Join Query

Second part - more than one user in one car at the same time: SQLFiddle - correlated Query and Join Query. Query below...

I use the correlated queries:

You will likely need indexes on userid and 'car'. However - please check the 'explain plan' to see how it mysql is accessing the data. And just try it :)

Overlapping cars per user

The query:

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`;      

The results:

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

Why it works? or How I think about it:

I use the correlated query so I don't have duplicates to deal with and it is probably the easiest to understand for me. There are other ways of expressing the query. Each has advantages and drawbacks. I want something I can easily understand.

Requirement: For each user ensure that they don't have two or more cars at the same time.

So, for each user record (AllCars) check the complete table (overlapCar) to see if you can find a different record that overlaps for the time of the current record. If we find one then select the current record we are checking (in allCars).

Therefore the overlap check is:

• the allCars userid and the overLap userid must be the same
• the allCars car record and the overlap car record must be different

• the allCars time range and the overLap time range must overlap.

  The time range check:

  Instead of checking for overlapping times use positive tests. The easiest approach, is to check it doesn't overlap, and apply a NOT to it.

One car with More than One User at the same time...

The query:

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`;

The results:

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

Edit:

In view of the comments, by @philipxy , about time ranges needing 'greater than or equal to' checks I have updated the code here. I havn't changed the SQLFiddles.

这篇关于查找特定用户MySQL的同一个表中的日期范围重叠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！