mysql:ORDER BY mysql表的列中元素的出现次数

本文介绍了mysql:ORDER BY mysql表的列中元素的出现次数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

有人可以帮我根据编号对MySQL表进行订购"吗?同一表的某一列中出现字符串的次数.

Can someone please help me to 'ORDER' a mysql table according to the no. of occurrences of a string in one of the columns of same table.

我想根据位置发生的次数重新排列表格.

I want to rearrange the table according to the number of times a location has occurred.

请参见以下示例:

id name location -- ----- -------- 1 Mark US 2 Mike US 3 Paul Australia 4 Pranshu India 5 Pranav India 6 John Canada 7 Rishab India

预期结果:

id name location -- ----- -------- 4 Pranshu India 5 Pranav India 7 Rishab India 1 Mark US 2 Mike US 3 Paul Australia 6 John Canada

我尝试了以下查询:

SELECT *, COUNT(location) AS count FROM `tablename` GROUP BY `location` ORDER BY count DESC;

但是它显示了以下结果省略了重复出现的位置:

But it showed me the following result omitting the repeating occurrence of location:

id name location -- ----- -------- 4 Pranshu India 1 Mark US 3 Paul Australia 6 John Canada

推荐答案

SELECT x.* FROM my_table x JOIN (SELECT location, COUNT(*) total FROM my_table GROUP BY location) y ON y.location = x.location ORDER BY total DESC , id;