MySQL 在共享主机上无效使用组功能

发布于 2024-09-19 19:36:26 字数 444 浏览 7 评论 0原文

我需要知道某个城市中比赛场次最多的球队。

SELECT COUNT(am_matches.team_id) AS matches_count,
am_matches.team_id AS team_id
FROM am_matches
LEFT JOIN am_team ON (am_matches.team_id = am_teams.id)
WHERE am_matches.status = '3' AND am_teams.city_id = '$city_id'
GROUP BY am_matches.team_id
ORDER BY COUNT(am_matches.team_id) DESC LIMIT 1

这适用于本地服务器（wamp），但在共享主机上会抛出“无效使用组功能”。还有其他方法可以得到这个吗？我需要进行很多类似的查询，所以我想知道一种更易读的方法来获取相似的值。谢谢。

原文

I need to know the team with highest number of matches in a certain city.

SELECT COUNT(am_matches.team_id) AS matches_count,
am_matches.team_id AS team_id
FROM am_matches
LEFT JOIN am_team ON (am_matches.team_id = am_teams.id)
WHERE am_matches.status = '3' AND am_teams.city_id = '$city_id'
GROUP BY am_matches.team_id
ORDER BY COUNT(am_matches.team_id) DESC LIMIT 1

This works on a local server (wamp), but on a shared host throws "Invalid use of group function". Is there another way of getting this? I need to make a lot of similar querys so I would like to know a more readable approach of getting similar values.
Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

恬淡成诗 2024-09-26 19:36:26

您可以按照注释所述按“1”排序，也可以使用列别名“matches_count”。为了清晰和可读性，最好使用别名，而不是数字索引。您只会在 HAVING 子句中重新调用分组函数，而不会在 ORDER 中重新调用。当 ORDER 看到您的结果时，数据已全部组装完毕并刚刚进行排序。

ORDER BY matches_count DESC LIMIT 1

You can order by "1" as the comment said, or you can use the column alias "matches_count". It's preferable and expected to use the alias, not the numerical index, for clarity and readability sake. You would only re-invoke grouping functions in a HAVING clause, never in ORDER. By the time ORDER sees your results, the data is all assembled and just gets sorted.