MySQL查询显示具有最大计数值的记录与其他列值的组？

为此，请使用GROUPBYHAVING子句。让我们首先创建一个表-

create table DemoTable ( Value int );

使用插入命令在表中插入一些记录-

insert into DemoTable values(88); insert into DemoTable values(88); insert into DemoTable values(88); insert into DemoTable values(99); insert into DemoTable values(99); insert into DemoTable values(99); insert into DemoTable values(99); insert into DemoTable values(100); insert into DemoTable values(100); insert into DemoTable values(88); insert into DemoTable values(88);

使用select语句显示表中的所有记录-

select *from DemoTable;

这将产生以下输出-

+-------+ | Value | +-------+ | 88 | | 88 | | 88 | | 99 | | 99 | | 99 | | 99 | | 100 | | 100 | | 88 | | 88 | +-------+ 11 rows in set (0.00 sec)

以下是查询，以在具有其他列的组中显示具有最大计数值的记录。在这里，我们在列中重复了值，并对值进行排序。在ORDERBYDESC之后，我们将获取第一个值并将其计数显示在新列“NumberOfCount”中-

select Value,count(*) as NumberOfCount from DemoTable group by Value having count(*)=(select count(*) as NumberOfCount from DemoTable group by Value order by NumberOfCount desc limit 1);

这将产生以下输出-

+-------+---------------+ | Value | NumberOfCount | +-------+---------------+ | 88 | 5 | +-------+---------------+ 1 row in set (0.00 sec)