时间戳

首页 > 解决方案 > 本赛季出场球员的职业生涯平均值

mysql - 本赛季出场球员的职业生涯平均值

问题描述

我试图列出球员在他们职业生涯中的平均水平，但只包括在 2018 年至少打过一场比赛的球员，但我的查询只是回归球员在 2018 年“只”打过，而不是所有球员的职业生涯平均水平2018 年至少参加过一场比赛。

SELECT PlayerID
     , Surname
     , AKA
     , COUNT(*) AS Played
     , ROUND(AVG(Kicks),1) AS K
     , ROUND(AVG(Handballs),1) AS HB
     , ROUND(AVG(Marks),1) AS M
     , Season
  FROM `PlayerDetails` 
 WHERE Team = 'Team A'
 GROUP 
    BY PlayerID 
HAVING Season = 2018
 ORDER 
    BY Surname ASC

标签： mysqlhaving-clause

解决方案

您需要删除您的HAVING条款并添加到您的WHERE条款：

SELECT PlayerID, Surname,AKA, COUNT(*) AS Played, ROUND(AVG(Kicks),1) AS K, ROUND(AVG(Handballs),1) AS HB, ROUND(AVG(Marks),1) AS M, Season
FROM `PlayerDetails` p1
WHERE Team = 'Team A'
   AND EXISTS (SELECT * FROM `PlayerDetails` p2 WHERE p2.playerID = p1.playerID AND Season = 2018)
GROUP BY PlayerID 
ORDER BY Surname ASC

您所拥有的HAVING子句 - 正如您所发现的那样 - 仅将查询限制为 2018 年的结果。EXISTS我添加到的子句WHERE将查找任何在 2018 年参加过比赛的球员。

推荐阅读