MySQL查询：筛选6月16日09:00-18:00可用的顾问

To solve this, we need to exclude any advisor who has an unavailability period that overlaps with the target time window. Here's a straightforward approach using MySQL:

Core Logic #

An advisor is unavailable if their unavailability record overlaps with 2018-06-16 09:00:00 to 2018-06-16 18:00:00. Two time ranges overlap if:

• The unavailability start time is before the target window ends, and
• The unavailability end time is after the target window starts.

We’ll use this condition to flag unavailable advisors, then select all advisors not in that group.

Solution Query #

Assuming your unavailability table is named advisor_unavailability, here’s the query:

SELECT DISTINCT c_id
FROM advisor_unavailability
WHERE c_id NOT IN (
    SELECT DISTINCT c_id
    FROM advisor_unavailability
    WHERE start_date < '2018-06-16 18:00:00'
      AND end_date > '2018-06-16 09:00:00'
);

Breakdown #

1. Subquery: The inner query grabs all advisors with at least one overlapping unavailability period. DISTINCT ensures we don’t get duplicate advisor IDs.
2. Main Query: We select all distinct advisor IDs that aren’t in the list of unavailable advisors from the subquery.

Alternative (More Efficient for Large Datasets) #

If you’re working with a big table, a LEFT JOIN can perform better than NOT IN:

SELECT DISTINCT au.c_id
FROM advisor_unavailability au
LEFT JOIN advisor_unavailability au_unavail
    ON au.c_id = au_unavail.c_id
    AND au_unavail.start_date < '2018-06-16 18:00:00'
    AND au_unavail.end_date > '2018-06-16 09:00:00'
WHERE au_unavail.c_id IS NULL;

This joins the table to itself on advisor ID and the overlap condition, then filters out rows where a matching unavailable record exists (indicated by au_unavail.c_id IS NULL).

Expected Result #

Based on your provided data, the available advisors are 937 and 939—advisor 938 is excluded because their 2018-06-16 14:00–17:00 unavailability overlaps with the target window.

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