在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 教程