我怎样才能找到签到和结账的地方（Overbooking）

I have a MYSQL table that looks like this

我有一个看起来像这样的MYSQL表

---------------------------------------------
id | checkin            | checkout
---------------------------------------------
1  |2015-12-15 14:00:00 | 2015-12-19 11:00:00
2  |2016-01-17 14:00:00 | 2016-01-19 11:00:00
3  |2015-10-29 14:00:00 | 2015-11-01 11:00:00
4  |2015-10-30 14:00:00 | 2015-11-05 11:00:00

So for example id 3 is crossing with id 4 which means it is overbooked what query can i write to detect it? Thanks

所以例如id 3与id 4交叉，这意味着它被超量预订了我可以编写哪些查询来检测它？谢谢

1 个解决方案

#1

You can join the table against itself where the checkin is between checkin and checkout.

您可以在签入和结帐之间签入自己的桌子。

Simple way with an OR in the join condition

在连接条件中使用OR的简单方法

SELECT *
FROM some_table a
INNER JOIN some_table b
ON (b.checkin BETWEEN a.checkin AND a.checkout
OR b.checkout BETWEEN a.checkin AND a.checkout)
AND a.id != b.id

Using a UNION which might be more efficient.

使用可能更有效的UNION。

SELECT *
FROM some_table a
INNER JOIN some_table b
ON b.checkin BETWEEN a.checkin AND a.checkout
AND a.id != b.id
UNION
SELECT *
FROM some_table a
INNER JOIN some_table b
ON b.checkout BETWEEN a.checkin AND a.checkout
AND a.id != b.id

EDIT

编辑

If the time bands can have the same recorded checkin / check out without being regarded as overlapping:-

如果时间段可以具有相同的记录签入/签出而不被视为重叠： -

SELECT *
FROM some_table a
INNER JOIN some_table b
ON b.checkin > a.checkin AND b.checkin < a.checkout
AND a.id != b.id
UNION
SELECT *
FROM some_table a
INNER JOIN some_table b
ON b.checkout > a.checkin AND b.checkout < a.checkout
AND a.id != b.id

EDIT again

再次编辑

If you only want to count the date parts:-

如果您只想计算日期部分： -

SELECT *
FROM some_table a
INNER JOIN some_table b
ON DATE(b.checkin) > DATE(a.checkin) AND DATE(b.checkin) < DATE(a.checkout)
AND a.id != b.id
UNION
SELECT *
FROM some_table a
INNER JOIN some_table b
ON DATE(b.checkout) > DATE(a.checkin) AND DATE(b.checkout) < DATE(a.checkout)
AND a.id != b.id

#1