php - 关于 SQL 错误的困惑

Question

我对 PHP 中的 sql 语句有点困惑，因为我不使用 GROUP BY 函数。我正在尝试确定参加至少 10 场比赛的球员。因此，我在请求 COUNT(DISTINCT(gameid)) > 9 中添加以下内容，然后收到此 SQL 错误。

SELECT 
  SUM(kills) as sum_kills, 
  SUM(deaths) as sum_death, 
  SUM(teamkills) as sum_teamkills, 
  SUM(suizide) as sum_suizide, 
  SUM(points) as sum_points, 
  aliases.hash as name, 
  aliases.rang as rang, 
  COUNT(DISTINCT(gameid)) as sum_games, 
  playerid 
FROM 
  stats_rounds_players, 
  aliases 
WHERE 
  aliases.id = playerid 
  AND COUNT(DISTINCT(gameid)) > 9 
  AND aliases.hash != ''
GROUP BY 
  playerid 
ORDER BY 
  sum_points DESC

我收到以下错误消息:

#1111 - Invalid use of group function

如果您需要更多信息，请告诉我。

Answer 1

您需要对 WHERE COUNT(DISTINCT(gameid)) > 9 使用 HAVING，因为您不能将 WHERE 与聚合一起使用

See here

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

类似(未测试):

SELECT 
  SUM(kills) as sum_kills, 
  SUM(deaths) as sum_death, 
  SUM(teamkills) as sum_teamkills, 
  SUM(suizide) as sum_suizide, 
  SUM(points) as sum_points, 
  aliases.hash as name, 
  aliases.rang as rang, 
  COUNT(DISTINCT(gameid)) as sum_games, 
  playerid 
FROM 
  stats_rounds_players, 
  aliases 
WHERE 
  aliases.id = playerid 
  AND aliases.hash != ''
GROUP BY 
  playerid 
HAVING 
  COUNT(DISTINCT(gameid)) > 9 
ORDER BY 
  sum_points DESC