按最常出现的值 MYSQL 排序

Question

如何从我的表中订购此颜色列

color
------ ----
red   |
blue  | 
red   |
green |
blue  |
red   |
yellow|
blue  |
green |
red   |

对此

color
------ ----
red   |
red   | 
red   |
red   |
blue  |
blue  |
blue  |
green |
green |
yellow|

我尝试了以下代码，但它只返回一行数据，这不是我想要的。我希望它返回表中的所有值

SELECT *, COUNT(color) AS count FROM color_table ORDER BY count DESC

Answer 1

将表联接到返回每种颜色的计数器的查询：

select c.*
from color_table c inner join (
select color, count(*) counter  
from color_table
group by color
) t on t.color = c.color
order by t.counter desc

请参阅演示。
或：

select c.*
from color_table c
order by (select count(*) from color_table where color = c.color) desc

请参阅演示。

Answer 2

好吧，看看这个：

select b.*
from (
select color, count(1) cnt
from color_table
group by color
) as a
inner join color_table as b
on a.color = b.color
order by a.cnt desc, a.color

Answer 3

您可以使用窗口函数：

order by count(*) over (partition by color) desc;

Answer 4

您需要按颜色分组。否则，count(( 函数将只计算表中的所有记录。喜欢这个：

SELECT *, COUNT(color) AS count FROM color_table GROUP BY color ORDER BY count DESC