c# - 如何从mysql查询中多行的同一列中获取总值？

Question

我在 ASP.Net (C#) 中有一个项目连接到游戏数据库。我有一个 player 表，我想从具有相同 account_id 的所有玩家那里获取 playtime 行的总值。我最后的逻辑是这样的:

MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
String queryStr;
String connStringPlayer = System.Configuration.ConfigurationManager.ConnectionStrings["PlayerDB"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connStringPlayer);
conn.Open();
queryStr = "SELECT playtime FROM player.player WHERE account_id='" + AccountId + "' LIMIT 5";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
{
    Int32 countpt = (Int32)cmd.ExecuteScalar();
    String playcount = countpt.ToString();
    AccountInfoTotalGameMinuetsValue.Text = playcount;
}
conn.Close();

通过这种方式，我的AccountInfoTotalGameMinuetsValue 标签仅显示此account_id 的第一位玩家的游戏时间(此帐户中存在2 位玩家)。

Answer 1

SUM是你要找的

SUM([DISTINCT] expr)

Returns the sum of expr. If the return set has no rows, SUM() returns NULL. The DISTINCT keyword can be used to sum only the distinct values of expr.

SUM() returns NULL if there were no matching rows.

所以下面的查询将为您提供所有玩家的总数

SELECT account_id, SUM(playtime) FROM player.player GROUP BY account_id;

如果你只想要一个玩家的信息

SELECT account_id, SUM(playtime) FROM player.player 
WHERE account_id = some_id 
GROUP BY account_id;