如何从SUM()聚合结果中取MAX()？查询飞行里程最多的乘客

你的原SQL已经能正确计算每位乘客的总飞行里程，要获取里程总和最大的乘客，有几种常用实现方式：

方法一：子查询匹配最大值 #

先通过子查询算出所有乘客的总里程，再筛选出总里程等于最大值的记录：

SELECT TP.strFirstName, 
       TP.strLastName, 
       total_miles
FROM (
    SELECT TP.strFirstName, 
           TP.strLastName, 
           SUM(TF.intMilesFlown) AS total_miles
    FROM TPassengers AS TP
    JOIN TFlightPassengers AS TFP
         ON TP.intPassengerID = TFP.intPassengerID
    JOIN TFlights AS TF
         ON TFP.intFlightID = TF.intFlightID          
    GROUP BY TP.strLastName, TP.strFirstName
) AS passenger_miles
WHERE total_miles = (
    SELECT MAX(total_miles)
    FROM (
        SELECT SUM(TF.intMilesFlown) AS total_miles
        FROM TPassengers AS TP
        JOIN TFlightPassengers AS TFP
             ON TP.intPassengerID = TFP.intPassengerID
        JOIN TFlights AS TF
             ON TFP.intFlightID = TF.intFlightID          
        GROUP BY TP.strLastName, TP.strFirstName
    ) AS max_miles
);

如果你的SQL支持CTE（公共表表达式），可以简化嵌套子查询，让代码更易读：

WITH passenger_miles AS (
    SELECT TP.strFirstName, 
           TP.strLastName, 
           SUM(TF.intMilesFlown) AS total_miles
    FROM TPassengers AS TP
    JOIN TFlightPassengers AS TFP
         ON TP.intPassengerID = TFP.intPassengerID
    JOIN TFlights AS TF
         ON TFP.intFlightID = TF.intFlightID          
    GROUP BY TP.strLastName, TP.strFirstName
)
SELECT strFirstName, strLastName, total_miles
FROM passenger_miles
WHERE total_miles = (SELECT MAX(total_miles) FROM passenger_miles);

方法二：使用窗口函数（推荐，支持并列第一） #

如果存在多位乘客里程相同且都是最大值的情况，用RANK()或DENSE_RANK()可以保留所有并列记录，而ROW_NUMBER()只会返回其中一条：

SELECT strFirstName, strLastName, total_miles
FROM (
    SELECT TP.strFirstName, 
           TP.strLastName, 
           SUM(TF.intMilesFlown) AS total_miles,
           RANK() OVER(ORDER BY SUM(TF.intMilesFlown) DESC) AS mile_rank
    FROM TPassengers AS TP
    JOIN TFlightPassengers AS TFP
         ON TP.intPassengerID = TFP.intPassengerID
    JOIN TFlights AS TF
         ON TFP.intFlightID = TF.intFlightID          
    GROUP BY TP.strLastName, TP.strFirstName
) AS ranked_passengers
WHERE mile_rank = 1;

这里RANK()会给并列最大值的乘客都标记为排名1，若用ROW_NUMBER()则会随机给并列的乘客分配不同排名，根据需求选择即可。

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