本文介绍了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;
更多推荐
mysql:ORDER BY mysql表的列中元素的出现次数
发布评论