这是第一部分:每个用户重叠的汽车......
SQLFiddle - 关联查询和连接查询 http://sqlfiddle.com/#!9/af69b/5
第二部分 - 一辆车内同时有多个用户:SQLFiddle - 关联查询和连接查询 http://sqlfiddle.com/#!9/af69b/7。下面查询...
我使用相关查询:
您可能需要用户 ID 和“汽车”的索引。但是 - 请检查“解释计划”以了解 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 中)。
因此overlap检查是:
-
the allCars
userid
和overLap
userid
必须相同
-
the allCars
汽车记录和overlap
行车记录一定是不同的
-
the allCars
时间范围和overLap
时间范围必须重叠。
时间范围检查:
使用阳性测试代替检查重叠时间。最简单的方法是检查它不重叠,然后应用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
Edit:
鉴于 @philipxy 的评论,关于需要“大于或等于”检查的时间范围,我已在此处更新了代码。我没有改变SQLFiddles
.