SQL聚合查询错误

Question

我有3个这样的表

player(id,name,age,teamid)
team(id,name,sponsor,totalplayer,totalchampion,boss,joindate)
playerdetail(id,playerid,position,number,allstar,joindate)

我想选择团队信息包括姓名，赞助商，总球员，总冠军，老板， 球员的平均年龄，全明星球员的数量

我编写了如下t-sql

SELECT T.NAME,T.SPONSOR,T.TOTALPLAYER,T.TOTALCHAMPION,T.BOSS,T.JOINDATE,
AVG(P.AGE) AS AverageAge,COUNT(D.ALLSTAR) As AllStarPlayer
FROM Team T,Player P,PlayerDetail D 
WHERE T.ID=P.TID AND P.ID=D.PID

，但它不起作用，错误消息是

'列'Team.Name'在选择列表中无效，因为它不是 包含在聚合函数或 GROUP BY 子句中。'

谁能帮助我？ 提前谢谢！

Answer 1

在

GROUP BY 
      T.NAME,T.SPONSOR,T.TOTALPLAYER,T.TOTALCHAMPION,T.BOSS,T.JOINDATE

大多数 RDBMS 中（MySQL 除外，它会为您猜测），列必须是聚合的（COUNT、AVG) 或在 GROUP BY 中

另外，您应该使用显式 JOIN。
这更清晰、更清晰、更难以修改你的代码

SELECT 
    T.NAME, T.SPONSOR, T.TOTALPLAYER, T.TOTALCHAMPION, T.BOSS, T.JOINDATE,
    AVG(P.AGE) AS AverageAge,
    COUNT(D.ALLSTAR) As AllStarPlayer
FROM 
    Team T
    JOIN
    Player P ON T.ID=P.TID
    JOIN
    PlayerDetail D  ON P.ID=D.PID
GROUP BY 
    T.NAME, T.SPONSOR, T.TOTALPLAYER, T.TOTALCHAMPION, T.BOSS, T.JOINDATE;

Answer 2

鉴于您需要每个团队的这些数据，并且 team.ID 唯一标识团队，我建议如下：

SELECT max(T.NAME) As TeamName,
       max(T.SPONSOR) As Sponsor,
       max(T.TOTALPLAYER) As TotalPlayers,
       max(T.TOTALCHAMPION) As TotalChampions,
       max(T.BOSS) As Boss,
       max(T.JOINDATE) As JoinDate,
       AVG(P.AGE) AS AverageAge,
       COUNT(D.PID) As AllStarPlayer
FROM Team T
join Player P on T.ID=P.TID 
left join PlayerDetail D on P.ID=D.PID and D.ALLSTAR = 'Y'
group by T.ID

Answer 3

使用：

SELECT T.NAME,T.SPONSOR,T.TOTALPLAYER,T.TOTALCHAMPION,T.BOSS,T.JOINDATE,
AVG(P.AGE) AS AverageAge,COUNT(D.ALLSTAR) As AllStarPlayer
FROM Team T
JOIN Player P ON T.ID = P.TEAMID
JOIN PlayerDetail D ON P.ID = D.PLAYERID
GROUP BY T.NAME,T.SPONSOR,T.TOTALPLAYER,T.TOTALCHAMPION,T.BOSS,T.JOINDATE