时间戳

首页 > 解决方案 > 如何总结球员的所有进球数

mysql - 如何总结球员的所有进球数

问题描述

我想问一下我如何总结过去7天球员的所有进球数。
表的名称是player_games，包含玩游戏日期的字段是game_played。

我尝试对分数求和，但我收到 0 作为分数的结果。不包括最后一周。

SELECT player_id as playerID, SUM('scores') as scores 
FROM `player_games` 
GROUP BY player_id 
ORDER BY SUM('scores') DESC

标签： mysql

解决方案

您可以使用DATE_SUBand使用以下内容CURDATE：

SELECT player_id AS playerID, SUM(scores) AS scores 
FROM player_games
WHERE game_played BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()
GROUP BY player_id 
ORDER BY SUM(scores) DESC

您也可以直接在WHERE条件上使用日期计算，而无需使用DATE_SUB：

SELECT player_id AS playerID, SUM(scores) AS scores 
FROM player_games
WHERE game_played BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE()
GROUP BY player_id 
ORDER BY SUM(scores) DESC

推荐阅读