我绝不是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
我觉得我在这里碰壁,我很高兴能够在单独的查询中进行这些比较。我需要将它们显示在一个表中,但是我总是可以将结果合并。
我已经进行了研究并进行了数小时的测试,但无法达到想要的结果。
我已经尝试过这些帖子(它们并不是我所需要的,但是足够接近,或者我以为如此):
这是我能找到的最接近的解决方案,但是当我在单个表(将表连接到自身)上尝试时,我得到了疯狂的结果:检查表是否存在时间重叠?
编辑
作为临时解决方案,我采用了另一种方法,类似于我在研究中发现的帖子(上)。现在,我将检查新租车/转让日期是否与表格中的任何日期范围重叠。如果是这样,我将保存与日期重叠的行的ID。这样,至少我将能够标记重叠并允许用户查看标记的行并手动解决任何重叠。
感谢所有为此提供帮助的人,除非有人有更好的方法来实现,否则我将在接下来的24小时内将philipxy答案标记为已选择的答案。我毫不怀疑,按照他的回答,我最终将能够达到我所需要的结果。目前,我需要采用任何可行的解决方案,因为我需要在接下来的几天中完成我的项目,因此改变了方法。
编辑#2
这两个答案都是极好的,对于发现这篇文章与我有同样问题的人,阅读它们并看看小提琴!:)他们完成了许多惊人的脑力劳动!暂时,我不得不采用在我的#1编辑中提到的解决方案,但是我将通过@Ryan Vincent方法+ @philipxy编辑/注释来适应我的查询,以忽略最初的一天重叠。
这是第一部分:每个用户的汽车重叠...
第二部分-同一辆车中同时有多个用户: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中)。
因此,重叠检查为:
的allCars
userid
和overLap
userid
必须是相同的
在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
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句