我希望有人能指出我為什么我的資料不是按 flight_count 列排序的正確方向？
SELECT pilot_id,
pilot_firstname,
pilot_lastname,
pilot_email,
licence_num,
flight_count
FROM pilots
INNER JOIN
( SELECT pilot_id,
COUNT(flight_id) AS 'flight_count'
FROM flights
GROUP BY pilot_id
ORDER BY flight_count DESC
) as a
USING (pilot_id);
uj5u.com熱心網友回復：
將排序條件移到外部select：
SELECT p.pilot_id, p.pilot_firstname, p.pilot_lastname, p.pilot_email, p.licence_num,
fc.flight_count
FROM pilots p
JOIN (
SELECT pilot_id, COUNT(flight_id) AS flight_count
FROM flights
GROUP BY pilot_id
) as fc
on fc.pilot_id = p.pilot_id
ORDER BY fc.flight_count DESC;
請注意，您不應該“參考”列別名來分隔它們，并且名稱按原樣很好；顯式使用（有意義的）表別名通常也是一個好主意，它有助于提高可讀性，并且還意味著如果列顯式別名，則查詢優化器要做的作業更少。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/394806.html
標籤：mysql