从一个SUM()中找到MAX()。

huangapple go评论85阅读模式
英文:

Finding MAX() from a SUM()

问题

我需要获取飞行里程最多的乘客。

  1. SELECT TP.strFirstName,
  2. TP.strLastName,
  3. SUM(TF.intMilesFlown) AS TotalMilesFlown
  4. FROM TPassengers AS TP
  5. JOIN TFlightPassengers AS TFP
  6. ON TP.intPassengerID = TFP.intPassengerID
  7. JOIN TFlights AS TF
  8. ON TFP.intFlightID = TF.intFlightID
  9. GROUP BY TP.strLastName,
  10. TP.strFirstName
  11. ORDER BY TotalMilesFlown DESC
  12. LIMIT 1;
英文:

I need to get the passenger with the most miles flown.

  1. SELECT TP.strFirstName,
  2. TP.strLastName,
  3. sum(TF.intMilesFlown)
  4. FROM TPassengers AS TP
  5. JOIN TFlightPassengers AS TFP
  6. ON TP.intPassengerID = TFP.intPassengerID
  7. JOIN TFlights AS TF
  8. ON TFP.intFlightID = TF.intFlightID
  9. GROUP BY TP.strLastName,
  10. 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 1ORDER BY..DESC 来获取最大的行。

  1. SELECT TOP 1 TP.strFirstName,
  2. TP.strLastName,
  3. sum(TF.intMilesFlown)
  4. FROM TPassengers AS TP
  5. JOIN TFlightPassengers AS TFP
  6. ON TP.intPassengerID = TFP.intPassengerID
  7. JOIN TFlights AS TF
  8. ON TFP.intFlightID = TF.intFlightID
  9. GROUP BY TP.strLastName,
  10. TP.strFirstName
  11. ORDER BY SUM(TF.intMilesFlown) DESC;
英文:

You can use TOP 1 with an ORDER BY..DESC to get the max row.

  1. SELECT TOP 1 TP.strFirstName,
  2. TP.strLastName,
  3. sum(TF.intMilesFlown)
  4. FROM TPassengers AS TP
  5. JOIN TFlightPassengers AS TFP
  6. ON TP.intPassengerID = TFP.intPassengerID
  7. JOIN TFlights AS TF
  8. ON TFP.intFlightID = TF.intFlightID
  9. GROUP BY TP.strLastName,
  10. TP.strFirstName
  11. ORDER BY SUM(TF.intMilesFlown) DESC;

huangapple
  • 本文由 发表于 2023年5月28日 08:27:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/76349530.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定