SQL中的嵌套查询/连接

lymnna71 于 2022-10-03 发布在 Mysql

关注(0)|答案(2)|浏览(156)

问题：列出球队的名称和所有胜数超过平均水平的球队的胜数。按获胜次数降序对结果进行排序。

SELECT mlb_team.name, mlb_team.wins, AVG(mlb_team.wins)
FROM mlb_team
GROUP BY mlb_team.name
HAVING mlb_team.wins > AVG(mlb_team.wins);

我当前的解决方案是给出错误错误代码：1055。SELECT LIST的表达式#2不在GROUP BY子句中，它包含非聚合列‘Comp3421.mlb_Team.ins’，该列在函数上不依赖于o。

mysql

来源：https://stackoverflow.com/questions/73920032/nested-queries-join-in-sql

2条答案

按热度按时间

nxowjjhe1#

SELECT mlb_team.name, SUM(mlb_team.wins)
FROM mlb_team
GROUP BY mlb_team.name
HAVING SUM(mlb_team.wins) > AVG(mlb_team.wins)
ORDER BY SUM(mlb_team.wins) DESC;

注意：我不熟悉MySQL，但基本上应该是这样的。

赞(0）回复(0）举报 2022-10-03

nhhxz33t2#

GROUP BY需要未聚合的每一列。

在您的代码中，GROUP BY需要mlb_team.name和mlb_team.wins，而不仅仅是mlb_team.name。

以下是代码：

SELECT mlb_team.name, mlb_team.wins, AVG(mlb_team.wins)
FROM mlb_team
GROUP BY mlb_team.name,mlb_team.wins
HAVING mlb_team.wins > AVG(mlb_team.wins);

赞(0）回复(0）举报 2022-10-03

我来回答

SQL中的嵌套查询/连接

2条答案

相关问题

热门标签

最新问答