显示MySQL中某列中重复记录的数量并排序结果
为此，您需要使用GROUPBY作为列名。要获得计数，请使用COUNT（*）并使用ORDERBY对结果进行排序。以下是语法-
select count(*) as anyAliasName from yourTableName group by yourColumnName order By yourAliasName DESC;
让我们首先创建一个表-
mysql> create table DemoTable ( Number int );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values(90); mysql> insert into DemoTable values(100); mysql> insert into DemoTable values(110); mysql> insert into DemoTable values(90); mysql> insert into DemoTable values(100); mysql> insert into DemoTable values(90); mysql> insert into DemoTable values(120); mysql> insert into DemoTable values(100); mysql> insert into DemoTable values(90); mysql> insert into DemoTable values(120);
使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+--------+ | Number | +--------+ | 90 | | 100 | | 110 | | 90 | | 100 | | 90 | | 120 | | 100 | | 90 | | 120 | +--------+ 10 rows in set (0.00 sec)
以下是查询以显示重复记录的数量并对结果进行排序-
mysql> select count(*) as Occured from DemoTable group by Number order By Occured DESC;
这将产生以下输出-
+---------+ | Occured | +---------+ | 4 | | 3 | | 2 | | 1 | +---------+ 4 rows in set (0.00 sec)