带有 Order By 的 SQL Group By [英] 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

这篇关于带有 Order By 的 SQL Group By的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！