酒店预订系统可用房间查询：MySQL复杂SQL语句开发需求

Here's a SQL query that returns users along with the rooms available for the entire period (2018-04-02 to 2018-04-04):

SELECT 
    user,
    GROUP_CONCAT(room_id ORDER BY room_id SEPARATOR ', ') AS available_rooms
FROM (
    -- First, filter entries to the target date range and validate full availability
    SELECT user, room_id
    FROM rooms_available
    WHERE date BETWEEN '2018-04-02' AND '2018-04-04'
    GROUP BY user, room_id
    -- Ensure the room is available on every day in the range
    HAVING COUNT(*) = DATEDIFF('2018-04-04', '2018-04-02') + 1
) AS valid_room_entries
GROUP BY user;

How It Works #

Let’s break down the logic step by step:

1. Filter to Target Date Range: The inner query first narrows down all entries to only those within your desired date window (2018-04-02 to 2018-04-04).
2. Validate Full Availability: We group entries by user and room_id, then use HAVING COUNT(*) = ... to check if the room has an entry for every day in the range. The DATEDIFF(end_date, start_date) + 1 calculates the total number of days in the period (3 days in this case).
3. Aggregate by User: The outer query takes the valid user-room pairs and uses GROUP_CONCAT to list all available rooms per user in a readable comma-separated format.

Expected Output #

Running this query against your sample data will produce:

User 2 doesn’t appear because room 11 is missing an entry for 2018-04-03, so it’s not available for the entire period.

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