SQL 获取列值等于最大值的行

Question

我通过查询select count(category), name from product natural join supplier group by name;得到了下表；:

 count |   nome    
-------+-----------
     1 | CandyCorp
     1 | Nike
     1 | DrinksInc
     7 | Mutante
     1 | Colt
     7 | Mazzetti

现在我只想获取计数等于计数列最大值的行(在本例中为 7)，得到:

 count |   nome    
-------+-----------
     7 | Mutant
     7 | Mazzetti

编辑:我通过创建一个临时表让它工作:

create table auxtable as (select count(categoria),name from product natural join supplier group by name);

select name from auxtable a1 join (select max(count) as countMax from auxtable) a2 on a1.count=a2.countMax;

drop table auxtable;

有没有办法在单个查询中做到这一点？

Answer 1

您可以使用 CTE 代替临时表:

with auxtable as (select count(categoria),name from product natural join supplier group by name)
select name from auxtable a1 
join (select max(count) as countMax from auxtable) a2 on a1.count=a2.countMax;