如何用SQL查询仅执行过1次飞行任务的飞行员信息？

Hey there! Let's break down why your original query isn't returning results, then fix it properly.

What's wrong with your current query? #

Your main issue is using the COUNT() aggregate function directly in the WHERE clause. Here's why that doesn't work:

• The WHERE clause filters individual rows before any grouping or aggregation happens. At that point, the database hasn't calculated the total number of trips per pilot yet.
• Aggregate functions like COUNT() need to work with grouped data, so you have to use the HAVING clause instead—it filters results after grouping and aggregation are done.

Correct Solutions #

Here are two reliable ways to get the pilots who've flown exactly 1 trip:

1. Subquery First (Clean and Efficient)

First, we'll calculate which pilots have exactly 1 trip in the flies table, then join that result to the pilots table to get their details:

SELECT p.FirstName, p.LastName, p.PhoneNumber
FROM pilots p
INNER JOIN (
    -- Subquery to get pilots with exactly 1 trip
    SELECT PilotID
    FROM flies
    GROUP BY PilotID
    HAVING COUNT(TripID) = 1
) f ON p.PilotID = f.PilotID;

2. Join + Group By + Having

You can also join the tables first, then group by pilot and filter with HAVING:

SELECT p.FirstName, p.LastName, p.PhoneNumber
FROM pilots p
INNER JOIN flies f ON p.PilotID = f.PilotID
GROUP BY p.PilotID, p.FirstName, p.LastName, p.PhoneNumber
HAVING COUNT(f.TripID) = 1;

Note: We include all the pilot columns in GROUP BY to comply with strict SQL standards (like MySQL's ONLY_FULL_GROUP_BY mode). Since PilotID is the primary key of pilots, grouping by it alone would logically determine the other columns in most databases, but including them makes the query portable.

Why These Work #

• Both approaches first group the flies data by PilotID to count how many trips each pilot has.
• The HAVING COUNT(TripID) = 1 clause then filters down to only those pilots with exactly one trip.
• Finally, we join back to the pilots table to retrieve the name and phone number for those pilots.

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