SQL取得与列值的行等于最大值

  • Z时代
  • 2024-01-10
  • 分类：问答

我下表得到了与查询select count(category), name from product natural join supplier group by name;：SQL取得与列值的行等于最大值

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;

在单个查询中有没有办法解决这个问题？

回答：

，你可以用它代替临时表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;

回答：

可以使用rank() over (order by count(*) desc)通过数排名，然后就保持排名第一的项目：

select * from (
    select 
     rank() over (order by count(category) desc) rn, 
     name, count(category) 
    from product natural join supplier 
    group by name 
) t where rn = 1

http://sqlfiddle.com/#!15/26b68/1

以上是 SQL取得与列值的行等于最大值 的全部内容， 来源链接： utcz.com/qa/258417.html

回到顶部