mysql - 从MySQL列中第一行的值中减去所有行

这是我的选择查询

Select
  snpc_stats.gamedetail.Player,
  Sum(snpc_stats.gamedetail.Points + snpc_stats.gamedetail.Hits) As TotalPoints,
  COUNT(*) As 'Games Played',
  Sum(If(snpc_stats.gamedetail.Finish = 1, 1, 0)) As Wins,
  Sum(If(snpc_stats.gamedetail.Finish = 2, 1, 0)) As Second,
  Sum(If(snpc_stats.gamedetail.Finish = 3, 1, 0)) As Third,
  Round(Avg(snpc_stats.gamedetail.Finish),2) As 'Average Finish',
  Sum(snpc_stats.gamedetail.Hits) As `Total Hits`,
  Sum(snpc_stats.gamedetail.ChampFund) As ChampFund,
  Sum(snpc_stats.games.BuyIn) + (snpc_stats.gamedetail.ChampFund) As Cost,
  Sum(snpc_stats.gamedetail.Winnings) As Winnings,
  Sum(snpc_stats.gamedetail.Winnings) - (snpc_stats.games.BuyIn) - (snpc_stats.gamedetail.ChampFund) As 'Total Winnings',
  COUNT(snpc_stats.games.Round) As round

From
  snpc_stats.gamedetail Inner Join
  snpc_stats.games On snpc_stats.games.GameID =
  snpc_stats.gamedetail.GamesID

Where
  snpc_stats.games.Season = '2015 Season'

Group By
  snpc_stats.gamedetail.Player, snpc_stats.games.Season

Order By
  TotalPoints Desc

在TotalPoints列之后，我想添加一列，以显示每个玩家落后于领导者的点数，如下表：

Rank | Player | Total Points | Points Behind
 1      Bill       164            -
 2       Al        152            -12
 3       Ed        151            -13
 4      Jill       123            -41
 5      Bob        121            -43
 6      Joe        102            -62
 7      Dave        82            -82
 8      Rob         60            -104
 9      Doug        60            -104
10      Don         51            -113
11      Dan         30            -134

任何帮助将不胜感激！

最佳答案

解决方案（1）如果使用连接到数据库并运行查询的服务器端语言，则可以在某些变量中保留第一行的总点值，并从中减去所有后续行
解决方案（2）进行子查询以返回第一行的总点，并在查询的选择部分中使用它

Select
  snpc_stats.gamedetail.Player,
  Sum(snpc_stats.gamedetail.Points + snpc_stats.gamedetail.Hits) As TotalPoints,
  (Sum(snpc_stats.gamedetail.Points + snpc_stats.gamedetail.Hits) - (Select Sum(snpc_stats.gamedetail.Points + snpc_stats.gamedetail.Hits) From snpc_stats.gamedetail Inner Join snpc_stats.games On snpc_stats.games.GameID = snpc_stats.gamedetail.GamesID Where snpc_stats.games.Season = '2015 Season' Group By snpc_stats.gamedetail.Player, snpc_stats.games.Seaso Order By Sum(snpc_stats.gamedetail.Points + snpc_stats.gamedetail.Hits) Desc Limit 1)) As Points Behind,
  COUNT(*) As 'Games Played',
  Sum(If(snpc_stats.gamedetail.Finish = 1, 1, 0)) As Wins,
  Sum(If(snpc_stats.gamedetail.Finish = 2, 1, 0)) As Second,
  Sum(If(snpc_stats.gamedetail.Finish = 3, 1, 0)) As Third,
  Round(Avg(snpc_stats.gamedetail.Finish),2) As 'Average Finish',
  Sum(snpc_stats.gamedetail.Hits) As `Total Hits`,
  Sum(snpc_stats.gamedetail.ChampFund) As ChampFund,
  Sum(snpc_stats.games.BuyIn) + (snpc_stats.gamedetail.ChampFund) As Cost,
  Sum(snpc_stats.gamedetail.Winnings) As Winnings,
  Sum(snpc_stats.gamedetail.Winnings) - (snpc_stats.games.BuyIn) - (snpc_stats.gamedetail.ChampFund) As 'Total Winnings',
  COUNT(snpc_stats.games.Round) As round
From
  snpc_stats.gamedetail Inner Join
  snpc_stats.games On snpc_stats.games.GameID =
  snpc_stats.gamedetail.GamesID
Where
  snpc_stats.games.Season = '2015 Season'
Group By
  snpc_stats.gamedetail.Player, snpc_stats.games.Season
Order By
  TotalPoints Desc