mysql - 选择mysql中每组的顶行

Question

我有一个包含两列的表格 - 地区和职业

region   |  profession
India    |  IT
India    |  IT
US       |  HR
US       |  HR
India    |  HR

我想展示每个地区最受欢迎的职业。输出应该只是

Region | Profession
India  | IT
US     | HR

Answer 1

首先，获取每组结果的计数。然后，您需要为每个组建立一个顺序——mysql 可以使用用户定义的变量来做到这一点。

这是一个例子:

select region, profession
from (
  select region, profession, 
    @rn:=if(@oldregion=region,@rn+1,0) rn,
    @oldregion:=region
  from (
    select region, profession, count(*) cnt
    from yourtable
    group by region, profession) t, (select @rn:=0, @oldregion='') t2
  order by region, cnt desc) t3
where rn = 0

• SQL Fiddle Demo

<小时/>

这是使用相关子查询的另一个解决方案:

select distinct region, (
    select profession
    from yourtable t2
    where t.region = t2.region
    group by region, profession
    order by count(*) desc
    limit 1) as profession
from yourtable t

• More Fiddle