收到错误：列'people.playerID'在选择列表中无效，因为它不包含在聚合函数中

Question

我收到错误：

选择列表中的列"people.playerID"无效，因为它是不包含在聚合函数中

select 
p.playerid,NameGiven + ' ( ' + namefirst + ' ) ' + nameLast as [Full Name], 
teamid, myr,yearid, format(AVG(salary),'C') as [Player Average Salary]
from 
Salaries s, 
people p , 
(select playerid ,max(yearid) as myr 
from Salaries 
group by playerID) m
where 
s.playerID=p.playerID and 
m.playerID=s.playerID

Answer 1

您使用的是旧的join，而这不是好的join。我建议你不要使用它。阅读这里要改掉的坏习惯：使用老式连接

select 
p.playerid,NameGiven + ' ( ' + namefirst + ' ) ' + nameLast as [Full Name], 
teamid, myr,yearid, format(AVG(salary),'C') as [Player Average Salary]
from 
Salaries s 
Inner Join People p on  s.playerID=p.playerID 
Inner Join 
(select 
playerid ,max(yearid) as myr 
from 
Salaries 
group by 
playerID) m on m.playerID=s.playerID
Group By
P.PlayerID, NameGiven + ' ( ' + namefirst + ' ) ' + nameLast,
teamid, myr, yearid

您的错误已清除。。只需完整表达aggregate即可。。

Answer 2

首先，请避免旧的连接表风格。使用JOIN的ANSI样式

对于您的错误，基本上无论哪一列未聚合，都必须出现在GROUP BY子句中。在您的情况下，您错过了整个GROUP BY

select 
p.playerid,
NameGiven + ' ( ' + namefirst + ' ) ' + nameLast as [Full Name], 
teamid, 
myr,
yearid,
format(AVG(salary),'C') as [Player Average Salary]    
from       Salaries s
inner join people p    on s.playerID = p.playerID
inner join (
select playerid ,max(yearid) as myr 
from   Salaries 
group by playerID
) m        on m.playerID = s.playerID
group by
p.playerid,
NameGiven + ' ( ' + namefirst + ' ) ' + nameLast, 
teamid, 
myr,
yearid

Answer 3

要显示详细信息和这些详细信息的聚合值，可以使用窗口函数。假设你正在寻找被检查球员的平均工资(就像你对myr所做的那样(，这将是：

SELECT
p.playerid,
NameGiven + ' ( ' + namefirst + ' ) ' + nameLast AS [Full Name],
teamid,
MAX(yearid) OVER (PARTITION BY p.playerid) AS myr,
yearid,
AVG(salary) OVER (PARTITION BY p.playerid) AS [Player Average Salary]
FROM Salaries s
INNER JOIN people p ON s.playerID = p.playerID;

格式化应该在使用记录的客户端应用程序或报告中完成。