带有 Order By 的 SQL Group By

2021-11-20 00:00:00 sql mysql mysql-error-1111

我有一个标签表，想从列表中获取计数最高的标签.

I have a table of tags and want to get the highest count tags from the list.

示例数据如下

id (1) tag ('night') id (2) tag ('awesome') id (3) tag ('night')

使用

SELECT COUNT(*), `Tag` from `images-tags` GROUP BY `Tag`

让我完美地找回了我正在寻找的数据.但是，我想对它进行组织，以便标签计数最高的在前，并将其限制为仅向我发送前 20 个左右.

gets me back the data I'm looking for perfectly. However, I would like to organize it, so that the highest tag counts are first, and limit it to only send me the first 20 or so.

我试过了...

SELECT COUNT(id), `Tag` from `images-tags` GROUP BY `Tag` ORDER BY COUNT(id) DESC LIMIT 20

并且我不断收到无效使用组功能 - ErrNr 1111"

and I keep getting an "Invalid use of group function - ErrNr 1111"

我做错了什么?

我使用的是 MySQL 4.1.25-Debian

I'm using MySQL 4.1.25-Debian

推荐答案

在所有版本的 MySQL 中，只需为 SELECT 列表中的聚合取别名，并按别名排序:

In all versions of MySQL, simply alias the aggregate in the SELECT list, and order by the alias:

SELECT COUNT(id) AS theCount, `Tag` from `images-tags` GROUP BY `Tag` ORDER BY theCount DESC LIMIT 20

相关文章