基于发生次数的返回记录

Question

我在db表'fb_list‘中有以下记录，我想查询它，以便检索'Assoc’的计数按等级排序小于4的所有记录。

Rank    Name    Assoc
1   PLAYER1 CHN
2   PLAYER2 CHN
3   PLAYER3 CHN
4   PLAYER4 CHN
5   PLAYER5 JPN
6   PLAYER6 BRA
7   PLAYER7 TPE
8   PLAYER8 CHN
9   PLAYER9 SWE
10  PLAYER10 GER

预期结果应返回记录1-3,5-7,9,10谢谢。

Answer 1

对于MySql 8.0+，使用ROW_NUMBER()窗口函数：

select t.`rank`, t.name, t.assoc
from (
  select *, row_number() over (partition by assoc order by `rank`) rn
  from fb_list
) t
where t.rn < 4

见演示。

以前的版本

select t.`rank`, t.name, t.assoc
from fb_list t
where (select count(*) from  fb_list where assoc = t.assoc and `rank` < t.rank) < 3 

见演示。

结果：

| rank | name     | assoc |
| ---- | -------- | ----- |
| 1    | PLAYER1  | CHN   |
| 2    | PLAYER2  | CHN   |
| 3    | PLAYER3  | CHN   |
| 5    | PLAYER5  | JPN   |
| 6    | PLAYER6  | BRA   |
| 7    | PLAYER7  | TPE   |
| 9    | PLAYER9  | SWE   |
| 10   | PLAYER10 | GER   |