MySQL 按每个值的 COUNT 排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2283305/
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
Order by COUNT per value
提问by Enkay
I have a table which stores IDs and the city where the store is located.
我有一张表，用于存储 ID 和商店所在的城市。
I want to list all the stores starting with the stores that are in the city where there are the most stores.
我想列出所有商店，从商店最多的城市中的商店开始。
TABLE
桌子
ID CITY
1 NYC
2 BOS
3 BOS
4 NYC
5 NYC
The output I want is the following since I have the most stores in NYC, I want all the NYC location to be listed first.
我想要的输出如下，因为我在纽约市拥有最多的商店，我希望首先列出所有纽约市的位置。
1 NYC
4 NYC
5 NYC
2 BOS
3 BOS
回答by MindStalker
SELECT count(City), City
FROM table
GROUP BY City
ORDER BY count(City);
OR
或者
SELECT count(City) as count, City
FROM table
GROUP BY City
ORDER BY count;
Ahh, sorry, I was misinterpreting your question. I believe Peter Langs answer was the correct one.
啊，对不起，我误解了你的问题。我相信 Peter Langs 的回答是正确的。
回答by Peter Lang
This one calculates the count in a separate query, joins it and orders by that count (SQL-Fiddle):
这个在单独的查询中计算计数，加入它并按该计数排序（SQL-Fiddle）：
SELECT c.id, c.city
FROM cities c
JOIN ( SELECT city, COUNT(*) AS cnt
FROM cities
GROUP BY city
) c2 ON ( c2.city = c.city )
ORDER BY c2.cnt DESC;
回答by Vincent Ramdhanie
This solution is not a very optimal one so if your table is very large it will take some time to execute but it does what you are asking.
此解决方案不是一个非常理想的解决方案，因此如果您的表非常大，则执行需要一些时间，但它可以满足您的要求。
select c.city, c.id,
(select count(*) as cnt from city c2
where c2.city = c.city) as order_col
from city c
order by order_col desc
That is, for each city that you come across you are counting the number of times that that city occurs in the database.
也就是说，对于您遇到的每个城市，您都在计算该城市在数据库中出现的次数。
Disclaimer: This gives what you are asking for but I would not recommend it for production environments where the number of rows will grow too large.
免责声明：这提供了您所要求的内容，但我不建议将其用于行数增长过大的生产环境。
回答by Corrie
SELECT `FirstAddressLine4`, count(*) AS `Count`
FROM `leads`
WHERE `Status`='Yes'
AND `broker_id`='0'
GROUPBY `FirstAddressLine4`
ORDERBY `Count` DESC
LIMIT 0, 8
