mysql:ORDER BY mysql表的列中元素的出现次数 [英] mysql: ORDER BY number of occurrences of an element in a column of mysql table

问题描述

有人可以帮我根据编号对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表的列中元素的出现次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！