查找同一表中的日期范围重叠，针对特定用户 MySQL

2021-11-26 00:00:00 alias intervals database mysql

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

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

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

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 上面的测试数据

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

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

比较同一表格中的两个日期范围

如何比较文本列的值来自同一张表

这是我能找到的最接近的解决方案，但是当我在单个表(将表与自身连接)上尝试时，我得到了疯狂的结果:检查表格的时间重叠?

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?

编辑

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

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.

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

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.

编辑#2

这两个答案都很棒，对于发现这篇文章与我有同样问题的人，请阅读它们并查看小提琴！:) 很多惊人的脑力工作进入了他们！暂时我不得不采用我在我的 #1 Edit 中提到的解决方案，但我将调整我的查询以采用 @Ryan Vincent 方法 + @philipxy 关于忽略最初一天重叠的编辑/评论.

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 - 相关查询和连接查询

第二部分 - 一辆车同时有多个用户:SQLFiddle - 相关查询和加入查询.下面查询...

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

我使用相关查询:

您可能需要关于用户 ID 和汽车"的索引.但是 - 请检查解释计划"以查看 mysql 如何访问数据.试试吧:)

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 :)

每位用户重叠的汽车

查询:

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

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

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

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.

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

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).

因此重叠检查是:

  • allCars userid 和 overLap userid 必须相同

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

allCars 时间范围和 overLap 时间范围必须重叠.

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

时间范围检查:

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

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.

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

查询:

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.

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.

相关文章