查找特定用户MySQL在同一张表中的日期范围重叠

  • Z时代
  • 2024-01-10
  • 分类：问答

我绝不是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与上面的测试数据

我已经尝试过这些帖子（它们并不是我所需要的，但是足够接近，或者我认为）：

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

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

这是我能找到的最接近的解决方案，但是当我在单个表（将表连接到自身）上尝试时，我得到了疯狂的结果：检查表是否存在时间重叠？

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

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

这两个答案都很出色，对于发现这篇文章与我有同样问题的人，阅读它们并看看小提琴！:)许多惊人的脑力劳动进入了他们！暂时，我不得不采用在我的＃1编辑中提到的解决方案，但是我将通过@Ryan

Vincent方法+ @philipxy编辑/注释来适应我的查询，以忽略最初的一天重叠。

回答：

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

SQLFiddle-相关查询和联接查询

第二部分-

同一辆车同时使用多个用户：SQLFiddle相关查询和Join查询。在下面查询…

我使用相关查询：

您可能需要在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中）。

因此， 重叠 检查为：

  • 的allCarsuserid和overLapuserid必须是相同的

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

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

时间范围检查：

不用检查重叠时间，而使用正面测试。最简单的方法是检查它是否重叠，然后对其应用a 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在同一张表中的日期范围重叠 的全部内容， 来源链接： utcz.com/qa/410510.html

回到顶部