mysql - SQL:如何过滤 GROUP BY 之外的结果

Question

原始问题

The following relations keep track of airline flight information:

Aircraft (aircraft_number, aircraft_make, cruisingrange)

Certified (pilot_id, aircraft_number)

Pilot (pilot_id, pilot_name, salary)

Note that italic attributes denote primary keys. In this scheme every pilot is certified for some aircraft. Write each of the following queries in SQL Format.

(ii) Find the names of pilots who can operate planes with a range greater than 2,000 miles but are not certified on any Boeing aircraft.

有人可以帮助我了解如何为此编写查询吗？

我的猜测是先join PILOT到CERTIFIED，然后join到AIRCRAFT，然后GROUP BY PILOT.pilot_id，但除此之外，我不确定如何过滤 pilot_id 以排除那些至少没有一架飞机且续航里程至少为 2000 且没有飞机的飞机aircraft_make“波音”？

非常感谢!

Answer 1

应该这样做:

select p.pilot_name
  from pilot p
  join certified c
    on p.pilot_id = c.pilot_id
  join aircraft a
    on c.aircraft_number = a.aircraft_number
  where a.cruisingrange > 2000
    and p.pilot_id not in (
          select c.pilot_id
            from certified c
            join aircraft a
              on c.aircraft_number = a.aircraft_number
            where a.aircraft_make = 'BOEING'
        )
  group by p.pilot_id