问题是我们试图以flightnum的形式显示前5名客户,因此我们不是获得前5名,而是获得前5名的flightnum计数,但只有一行(passengerid,passfname,passlname(
Select distinct p.PassengerID, p.PassFName, p.PassLName, Count(distinct
f.FlightNum)
from Passenger p, Flight f, PassengerFlight x
where p.passengerID = x.passengerid
and f.flightnum = x.flightnum
Order by f.flightnum DESC
limit 5
;
最大航班数显示前 5 名乘客(根据乘坐的航班总数(。
DISTINCT
有时在MySQL中有点不稳定。您可能最好使用 GROUP BY
,并且只对COUNT
使用DISTINCT
,如下所示:
SELECT p.PassengerID,
p.PassFName,
p.PassLName,
COUNT(DISTINCT f.FlightNum) AS numFlights
FROM Passenger p, Flight f, PassengerFlight x
WHERE p.passengerID = x.passengerid
AND f.flightnum = x.flightnum
GROUP BY p.PassengerID, p.PassFName, p.PassLName
ORDER BY numFlights DESC
LIMIT 5;