如何在MySQL中对两张表的数据求和并找出最大值？

只获取最大值的数值 #

直接用MAX()函数包裹你的计算式即可，这是最简洁的写法：

SELECT MAX(earning.Domestic + earning.Worldwide - IMDB.Budget) AS Max_Total
FROM IMDB
JOIN earning ON IMDB.Movie_id = earning.Movie_id

获取最大值对应的完整记录 #

如果需要查看这条最大结果对应的电影预算、本土票房、全球票房等信息，可以用子查询匹配最大值：

SELECT 
  IMDB.Budget, 
  earning.Domestic, 
  earning.Worldwide, 
  earning.Domestic + earning.Worldwide - IMDB.Budget AS Total
FROM IMDB
JOIN earning ON IMDB.Movie_id = earning.Movie_id
WHERE earning.Domestic + earning.Worldwide - IMDB.Budget = (
    -- 子查询计算最大值
    SELECT MAX(earning.Domestic + earning.Worldwide - IMDB.Budget)
    FROM IMDB
    JOIN earning ON IMDB.Movie_id = earning.Movie_id
)

如果存在多部电影并列最大值的情况，推荐用窗口函数RANK()来返回所有符合条件的记录：

WITH CalculatedTotals AS (
    SELECT 
      IMDB.Budget, 
      earning.Domestic, 
      earning.Worldwide, 
      earning.Domestic + earning.Worldwide - IMDB.Budget AS Total,
      -- 按Total降序排名，并列最大值会得到相同排名
      RANK() OVER(ORDER BY (earning.Domestic + earning.Worldwide - IMDB.Budget) DESC) AS RankNum
    FROM IMDB
    JOIN earning ON IMDB.Movie_id = earning.Movie_id
)
SELECT Budget, Domestic, Worldwide, Total
FROM CalculatedTotals
WHERE RankNum = 1

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