在MySQL的一栏中计算前10个最常出现的值？
要计算MySQL列中出现次数最多的前10个值，语法如下-
SELECT yourColumnName, count(*) FROM yourTableName GROUP BY yourColumnName ORDER BY count(*) DESC LIMIT 10;
为了理解上述语法，让我们创建一个表。创建表的查询如下-
mysql> create table countTop10Demo -> ( -> Value int -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into countTop10Demo values(10); mysql> insert into countTop10Demo values(1100); mysql> insert into countTop10Demo values(200); mysql> insert into countTop10Demo values(2100); mysql> insert into countTop10Demo values(1100); mysql> insert into countTop10Demo values(450); mysql> insert into countTop10Demo values(600); mysql> insert into countTop10Demo values(2100); mysql> insert into countTop10Demo values(150); mysql> insert into countTop10Demo values(16454); mysql> insert into countTop10Demo values(450); mysql> insert into countTop10Demo values(2350); mysql> insert into countTop10Demo values(1780); mysql> insert into countTop10Demo values(1345); mysql> insert into countTop10Demo values(34); mysql> insert into countTop10Demo values(2100); mysql> insert into countTop10Demo values(19034); mysql> insert into countTop10Demo values(1844); mysql> insert into countTop10Demo values(34); mysql> insert into countTop10Demo values(16454);
现在，您可以使用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(*) -> FROM countTop10Demo -> 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)