PostgreSQL查询未达预期：筛选所有水手均预订船只的日期

Hey there, let's break down why your current query isn't working and fix it to get the correct result (only '09/08/98').

The Problem with Your Original Query #

Your existing query checks for dates where there are no sailors who have never made any reservation at all (across all dates). Since both sailors in your data have at least one reservation somewhere, the EXCEPT subquery returns nothing, so NOT EXISTS is always true—hence it returns every date in the RESERVE table. This doesn't account for whether a sailor has a reservation on that specific date.

Correct Solutions #

We need to find dates where every sailor has at least one reservation that day. Here are two reliable approaches:

1. Aggregation & Count Matching

This method compares the number of unique sailors with reservations on a date to the total number of sailors:

SELECT r.DAY
FROM RESERVE r
GROUP BY r.DAY
HAVING COUNT(DISTINCT r.SID) = (SELECT COUNT(*) FROM SAILOR);

• The subquery (SELECT COUNT(*) FROM SAILOR) gets the total number of sailors (2 in your data).
• We group reservations by date, then count how many distinct sailors have a reservation that day.
• When this count equals the total number of sailors, we know every sailor booked a boat that date.

2. Double NOT EXISTS (Correlated Subquery)

This approach checks for dates where there are no sailors who don't have a reservation that day:

SELECT DISTINCT r1.DAY
FROM RESERVE r1
WHERE NOT EXISTS (
    SELECT s.SID
    FROM SAILOR s
    WHERE NOT EXISTS (
        SELECT 1
        FROM RESERVE r2
        WHERE r2.SID = s.SID AND r2.DAY = r1.DAY
    )
);

• For each date r1.DAY, we check if there's any sailor s who has no reservation on that date.
• If no such sailor exists (the inner NOT EXISTS is true for no sailors), then every sailor has a reservation that day, so we keep the date.

Testing with Your Data #

• For '09/05/98': Only sailor 64 has a reservation. The count is 1, which doesn't match the total sailor count (2), so it's excluded.
• For '09/08/98': Both sailors 64 and 74 have reservations. The count is 2, which matches, so it's included.

内容的提问来源于stack exchange，提问作者Rafael Pinto de Freitas