在MySQL中计算列中出现次数前10的最高值？

要计算MySQL中列中出现次数前10的最高值，语法如下 −

SELECT yourColumnName, count(*)
   FROM yourTableName
   GROUP BY yourColumnName
   ORDER BY count(*) DESC
   LIMIT 10;

要理解上述语法，让我们创建一个表。创建表的查询如下 −

mysql> create table countTop10Demo
   -> (
   -> Value int
   -> );
Query OK, 0 rows affected (0.74 sec)

使用insert命令将一些记录插入表中。查询如下 −

mysql> insert into countTop10Demo values(10);
Query OK, 1 row affected (0.12 sec)
mysql> insert into countTop10Demo values(1100);
Query OK, 1 row affected (0.11 sec)
mysql> insert into countTop10Demo values(200);
Query OK, 1 row affected (0.11 sec)
mysql> insert into countTop10Demo values(2100);
Query OK, 1 row affected (0.10 sec)
mysql> insert into countTop10Demo values(1100);
Query OK, 1 row affected (0.11 sec)
mysql> insert into countTop10Demo values(450);
Query OK, 1 row affected (0.28 sec)
mysql> insert into countTop10Demo values(600);
Query OK, 1 row affected (0.12 sec)
mysql> insert into countTop10Demo values(2100);
Query OK, 1 row affected (0.16 sec)
mysql> insert into countTop10Demo values(150);
Query OK, 1 row affected (0.16 sec)
mysql> insert into countTop10Demo values(16454);
Query OK, 1 row affected (0.10 sec)
mysql> insert into countTop10Demo values(450);
Query OK, 1 row affected (0.12 sec)
mysql> insert into countTop10Demo values(2350);
Query OK, 1 row affected (0.10 sec)
mysql> insert into countTop10Demo values(1780);
Query OK, 1 row affected (0.15 sec)
mysql> insert into countTop10Demo values(1345);
Query OK, 1 row affected (0.22 sec)
mysql> insert into countTop10Demo values(34);
Query OK, 1 row affected (0.15 sec)
mysql> insert into countTop10Demo values(2100);
Query OK, 1 row affected (0.08 sec)
mysql> insert into countTop10Demo values(19034);
Query OK, 1 row affected (0.12 sec)
mysql> insert into countTop10Demo values(1844);
Query OK, 1 row affected (0.11 sec)
mysql> insert into countTop10Demo values(34);
Query OK, 1 row affected (0.08 sec)
mysql> insert into countTop10Demo values(16454);
Query OK, 1 row affected (0.19 sec)

现在，您可以使用select语句显示表中的所有记录。查询如下 −

mysql> select *from countTop10Demo;

下面是输出结果 −

+-------+
| Value |
+-------+
| 10    |
| 1100  |
| 200   |
| 2100  |
| 1100  |
| 450   |
| 600   |
| 2100  |
| 150   |
| 16454 |
| 450   |
| 2350  |
| 1780  |
| 1345  |
| 34    |
| 2100  |
| 19034 |
| 1844  |
| 34    |
| 16454 |
+-------+
20 rows in set (0.00 sec)

下面是在MySQL中选择列中出现次数前10的最高值的查询语句 −

mysql> SELECT Value, count(*)
   -> FROMcountTop10Demo
   -> GROUP BY Value
   -> ORDER BY count(*) DESC
   -> LIMIT 10;

下面是输出结果 −

+-------+----------+
| Value | count(*) |
+-------+----------+
| 2100  | 3        |
| 1100  | 2        |
| 34    | 2        |
| 450   | 2        |
| 16454 | 2        |
| 1780  | 1        |
| 200   | 1        |
| 1345  | 1        |
| 19034 | 1        |
| 600   | 1        |
+-------+----------+
10 rows in set (0.00 sec)

阅读更多：MySQL 教程