MySQL 查询返回连接列的总和

Question

当我运行以下查询时，它应返回 9 条记录。

Select
 snpc_stats.gamedetail.Finish As Rank,
 snpc_stats.gamedetail.Player As Player,
(snpc_stats.gamedetail.Points + snpc_stats.gamedetail.Hits) As Points,
snpc_stats.gamedetail.RoundOut As `Round Out`,
snpc_stats.gamedetail.Hits As Hits,
snpc_stats.gamedetail.Points As `Game Points`,
snpc_stats.gamedetail.Hitman As Hitman,
snpc_stats.gamedetail.TimeOut As `Time Out`,
snpc_stats.games.BuyIn As `Buy In`,
snpc_stats.gamedetail.ChampFund As `Champ Fund`,
snpc_stats.gamedetail.Winnings As Winnings,
(replace(replace(snpc_stats.gamedetail.Winnings, '$', ''), ',', '') -
 replace(replace(snpc_stats.games.BuyIn, '$', ''), ',', '') -
 replace(replace(snpc_stats.gamedetail.ChampFund, '$', ''), ',', '')) As Net,
snpc_stats.games.Round,
snpc_stats.games.GameDate,
snpc_stats.games.Location

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

Where
snpc_stats.gamedetail.GamesID = 113

当我尝试将以下内容添加到 SELECT 的末尾时，它仅返回 1 条记录

, SUM(replace(replace(snpc_stats.games.BuyIn, '$', ''), ',', '')) As Purse

奖金、Buyin 和 ChampFund 是 VARCHAR 数据类型。我知道他们应该是 INT 但其他人最初制作了数据库......

如果有人能帮我弄清楚如何在汇总“BuyIn”列时返回所有 9 条记录，我将不胜感激!很接近了。

Answer 1

SUM 语句是一个聚合函数，这意味着它将添加返回的所有行，这就是为什么您只得到一行:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

如果您想添加某些属性，则必须在归档定义中添加 SUM()，并在查询末尾添加 GROUP BY 子句，并指定要分组的字段。像这样的东西:

GROUP BY player [, more fields you wanted to group by]