英文:
Finding MAX() from a SUM()
问题
我需要获取飞行里程最多的乘客。
SELECT TP.strFirstName,
TP.strLastName,
SUM(TF.intMilesFlown) AS TotalMilesFlown
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
ORDER BY TotalMilesFlown DESC
LIMIT 1;
英文:
I need to get the passenger with the most miles flown.
SELECT TP.strFirstName,
TP.strLastName,
sum(TF.intMilesFlown)
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
So this finds my sum but I don't know how to find the sum's max. I know it probably takes some subquery.
答案1
得分: 2
你可以使用 TOP 1 与 ORDER BY..DESC 来获取最大的行。
SELECT TOP 1 TP.strFirstName,
TP.strLastName,
sum(TF.intMilesFlown)
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
ORDER BY SUM(TF.intMilesFlown) DESC;
英文:
You can use TOP 1 with an ORDER BY..DESC to get the max row.
SELECT TOP 1 TP.strFirstName,
TP.strLastName,
sum(TF.intMilesFlown)
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
ORDER BY SUM(TF.intMilesFlown) DESC;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论