我可以用 MySQL 获取列中重复值的数量吗?
mysqlmysqli database更新于 2025/5/23 19:24:17
可以，您可以将 ORDER BY DESC 与 GROUP BY 结合使用。让我们首先创建一个表 −
mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> PostMessage varchar(100) -> ); Query OK, 0 rows affected (0.69 sec)
使用 insert 插入命令在表中插入一些记录 −
mysql> insert into DemoTable(PostMessage) values('Hi');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable(PostMessage) values('Hello');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable(PostMessage) values('Hi');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable(PostMessage) values('Awesome');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable(PostMessage) values('Hello');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable(PostMessage) values('Hi');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable(PostMessage) values('Awesome');
Query OK, 1 row affected (0.33 sec)
使用 select 语句显示表中的所有记录 −
mysql> select *from DemoTable;
输出
这将产生以下输出 −
+----+-------------+ | Id | PostMessage | +----+-------------+ | 1 | Hi | | 2 | Hello | | 3 | Hi | | 4 | Awesome | | 5 | Hello | | 6 | Hi | | 7 | Awesome | +----+-------------+ 7 rows in set (0.00 sec)
以下是获取 MySQL 数据库中某个值的最大数量的查询 −
mysql> select PostMessage,count(Id) from DemoTable group by PostMessage -> order by count(Id) DESC;
输出
这将产生以下输出 −
+-------------+-----------+ | PostMessage | count(Id) | +-------------+-----------+ | Hi | 3 | | Hello | 2 | | Awesome | 2 | +-------------+-----------+ 3 rows in set (0.00 sec)
如果只想要最高，可以使用以下查询 −
mysql> select PostMessage,count(Id) from DemoTable group by PostMessage having count(Id) > 2;
输出
这将产生以下输出 −
+-------------+-----------+ | PostMessage | count(Id) | +-------------+-----------+ | Hi | 3 | +-------------+-----------+ 1 row in set (0.00 sec)
相关文章
有用资源
mysql 参考教程 - 该教程包含有关 mysql 的更多信息:https://www.cainiaomax.com/mysql/
