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)