booking_person表人员预订时间重叠查询无结果问题求助

Hey there! Let's get to the bottom of why your EXISTS query isn't picking up those overlapping bookings. First, let's align on the overlap rule you specified: two bookings for the same person overlap if their time ranges intersect, but touching at exactly the start/end time (e.g., Booking 1 ends when Booking 2 starts) is allowed and shouldn't be flagged.

First, Let's Define the Correct Overlap Logic #

The key to catching all valid overlaps is checking that one booking starts before the other ends, and ends after the other starts. This covers every scenario where the time ranges actually overlap, excluding just the "touching" case.

Correct EXISTS Query #

Assuming your booking_person table has these fields: booking_id, person_id, start_time, end_time (adjust field names if yours are different), here's the query that should work:

SELECT y.*
FROM booking_person y
WHERE EXISTS (
    SELECT 1
    FROM booking_person b
    -- Match the same person
    WHERE b.person_id = y.person_id
    -- Exclude the booking itself from the check
      AND b.booking_id != y.booking_id
    -- The core overlap condition: ranges intersect but don't just touch
      AND y.start_time < b.end_time
      AND y.end_time > b.start_time
)

Why Your Original Query Might Have Failed #

Here are the most common issues that would prevent the query from returning results:

• Incorrect overlap condition: If you only checked one direction (e.g., y.end_time BETWEEN b.start_time AND b.end_time), you'd miss cases where the other booking's end time falls within y's range. The dual condition above covers all overlap scenarios.
• Field type mismatches: If start_time or end_time are stored as strings instead of datetime/timestamp types, string comparison logic won't work the way you expect for time ranges. Double-check your column types.
• Typos in field names: It's easy to mistype person_id or the time field names, which would break the join condition.
• Accidentally including the same booking: If you forgot to exclude b.booking_id = y.booking_id, the query might not behave as intended (though it usually still returns results, just duplicates).

Bonus: See Which Bookings Are Overlapping #

If you want to see exactly which pairs of bookings are overlapping (instead of just the overlapping bookings), use a JOIN instead of EXISTS to get both sides of the overlap:

SELECT 
    y.booking_id AS booking_a,
    b.booking_id AS booking_b,
    y.person_id,
    y.start_time AS a_start,
    y.end_time AS a_end,
    b.start_time AS b_start,
    b.end_time AS b_end
FROM booking_person y
JOIN booking_person b
  ON y.person_id = b.person_id
  -- Use < instead of != to avoid duplicate pairs (e.g., 1&2 and 2&1)
  AND y.booking_id < b.booking_id
  AND y.start_time < b.end_time
  AND y.end_time > b.start_time

This will give you a clean list of overlapping booking pairs without redundant entries.

内容的提问来源于stack exchange，提问作者Bablicious