MySQL 带有 Order By 的 SQL Group By
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27983/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Group By with an Order By
提问by maxsilver
I have a table of tags and want to get the highest count tags from the list.
我有一个标签表，想从列表中获取计数最高的标签。
Sample data looks like this
示例数据如下所示
id (1) tag ('night')
id (2) tag ('awesome')
id (3) tag ('night')
using
使用
SELECT COUNT(*), `Tag` from `images-tags`
GROUP BY `Tag`
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.
让我完美地找回了我正在寻找的数据。但是，我想对其进行组织，以便将最高的标签计数放在第一位，并将其限制为仅向我发送前 20 个左右。
I tried this...
我试过这个...
SELECT COUNT(id), `Tag` from `images-tags`
GROUP BY `Tag`
ORDER BY COUNT(id) DESC
LIMIT 20
and I keep getting an "Invalid use of group function - ErrNr 1111"
我不断收到“无效使用组功能 - ErrNr 1111”
What am I doing wrong?
我究竟做错了什么？
I'm using MySQL 4.1.25-Debian
我正在使用 MySQL 4.1.25-Debian
回答by Scott Noyes
In all versions of MySQL, simply alias the aggregate in the SELECT list, and order by the alias:
在所有版本的 MySQL 中，只需在 SELECT 列表中为聚合添加别名，并按别名排序：
SELECT COUNT(id) AS theCount, `Tag` from `images-tags`
GROUP BY `Tag`
ORDER BY theCount DESC
LIMIT 20
回答by Lasse V. Karlsen
MySQL prior to version 5 did not allow aggregate functions in ORDER BY clauses.
MySQL 5 之前的版本不允许在 ORDER BY 子句中使用聚合函数。
You can get around this limit with the deprecated syntax:
您可以使用已弃用的语法绕过此限制：
SELECT COUNT(id), `Tag` from `images-tags`
GROUP BY `Tag`
ORDER BY 1 DESC
LIMIT 20
1, since it's the first column you want to group on.
1，因为它是您要分组的第一列。
回答by jerhinesmith
I don't know about MySQL, but in MS SQL, you can use the column index in the order byclause. I've done this before when doing counts with group bys as it tends to be easier to work with.
我不了解 MySQL，但在 MS SQL 中，您可以在order by子句中使用列索引。我之前在用group bys进行计数时已经这样做了，因为它更容易使用。
So
所以
SELECT COUNT(id), `Tag` from `images-tags`
GROUP BY `Tag`
ORDER BY COUNT(id) DESC
LIMIT 20
Becomes
成为
SELECT COUNT(id), `Tag` from `images-tags`
GROUP BY `Tag`
ORDER 1 DESC
LIMIT 20
回答by JosephStyons
In Oracle, something like this works nicely to separate your counting and ordering a little better. I'm not sure if it will work in MySql 4.
在 Oracle 中，类似这样的东西可以很好地将您的计数和排序分开一些。我不确定它是否适用于 MySql 4。
select 'Tag', counts.cnt
from
(
select count(*) as cnt, 'Tag'
from 'images-tags'
group by 'tag'
) counts
order by counts.cnt desc
回答by Damien B
You can get around this limit with the deprecated syntax: ORDER BY 1 DESC
您可以使用已弃用的语法绕过此限制：ORDER BY 1 DESC
This syntax is not deprecated at all, it's E121-03 from SQL99.
这个语法根本没有被弃用，它是来自 SQL99 的 E121-03。
回答by Ashutosh Gupta
Try this query
试试这个查询
SELECT data_collector_id , count (data_collector_id ) as frequency
from rent_flats
where is_contact_person_landlord = 'True'
GROUP BY data_collector_id
ORDER BY count(data_collector_id) DESC
