SQL分组依据和排序依据 [英] SQL Group By with an Order By

问题描述

我有一张标签表，想从列表中获取数量最多的标签.

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

这篇关于SQL分组依据和排序依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！