根据最常见的表数据的前5个获取表结果

Question

示例表数据:

id | name | tool
--------------------
1 | bob  |   scissor
2 | mike |   knife
3 | john |   thread
4 | joe  |   ruler
5 | kim  |   marker
6 | dean |   board
7 | paul |   knife
8 | john |   scissor
9 | kim  |   ruler
10| mike |   scissor
11| mike |   board
12| joe  |   board
13| paul |   scissor
13| jake |   marker

，并希望从"工具"栏中获得最常见的前5个，这将告诉我诸如

之类的内容。

1 - scissor (4)
2 - board (3)
3 - knife (2)
4 - ruler (2)
5 - marker (2)

Answer 1

您可以按限制为5的计数顺序计数工具。

select tool,count(tool) as nr_count
from test
group by tool
order by nr_count desc limit 5;

结果:

tool    nr_count
scissor 4
board   3
knife   2
ruler   2
marker  2

https://dbfiddle.uk/?rdbms=mysql_8.0&小提琴= 4 c95c05c966d262547f752a2cb599b3b

请注意，您仍然有另一个工具，上面的查询没有考虑到计数2个结果。您需要通过

申请另一个订单