sql - 基于其他列的最常见的 2 个值

Question

我有这张表:

Team | Player
Barca | Messi
Chelsea | Lampard
Barca | Messi
Barca | Messi
Chelsea | Lampard
Chelsea | Drogba
Barca | Iniesta
Barca | Xavi
Barca | Iniesta
Barca | Puyol

所以我需要一个查询来检索每支球队中表现最好的两名球员。像这样:

Team | Player
Barca | Messi
Barca | Iniesta
Chelsea | Lampard
Chelsea | Drogba

Answer 1

您可以使用子查询/CTE 来计算每个球员的计数和排名，方法是根据每队计数的降序对行进行编号。然后从每队中拉出前两名球员:

查询 ( demo ):

with ranks as (
  select
    team, player, count(*) as count,
    row_number() over (partition by team order by count(*) desc) as teamrank
  from players p
  group by team, player
)
select
  team, player
from
  ranks
where teamrank <= 2
order by team, teamrank

结果:

|    TEAM |  PLAYER |
---------------------
|   Barca |   Messi |
|   Barca | Iniesta |
| Chelsea | Lampard |
| Chelsea |  Drogba |