mysql - 派生连接和聚合函数抛出错误

Question

我被困在一个查询中。我有两个表player和player_attributes，以player_api_id为主键。

我需要找到最年轻、最年长的玩家以及最年长和最年轻玩家的平均总体评分。

查询写入最年轻和最年长的玩家:

select player_name, birthday,YEAR(CURDATE()) - YEAR(birthday) as age from player where 
birthday=(select max(birthday) from player)
or
birthday=(select min(birthday) from player) 

查询所有玩家的平均综合评分:

SELECT player_api_id, avg(overall_rating) as avg_score,
FROM (
  SELECT player_api_id, overall_rating FROM player_attributes 
) as p
GROUP BY player_api_id;

加入时出错:

select player_api_id, avg(overall_rating),min(birthday),max(birthday) as avg_score
FROM (
  SELECT player_api_id, overall_rating FROM player_attributes
) as p
join
(select birthday from player) as p1
on p.player_api_id=p1.player_api_id
GROUP BY player_api_id;

我现在很困惑？

Answer 1

没有理由仅使用子查询来选择列。事实上，在 MySQL 中，这是一个非常非常糟糕的主意——因为 MySQL 实现了子查询。

所以，只需这样做:

select pa.player_api_id, avg(overall_rating) as avg_score,
       min(p.birthday), max(p.birthday)
from player_attributes pa join
     player p 
     on pa.player_api_id = p.player_api_id
group by pa.player_api_id;

我不确定其余的逻辑是否正确。但这至少应该修复语法错误。