mysql - Sql 查询查找按 id 排序和分组

Question

这个问题已经有答案了:

Get top n records for each group of grouped results (12 个回答)

已关闭 3 年前。

我有一个名为 personAndBookinMapping 的表列是:ma​​ppingId person_id book_id intreset_score book_name

现在我必须根据每个 person_id 的interest_score 找到前两条记录。基本上响应应该如下所示:

ma​​pping_id person_id book_id intreset_score book_name enter link description here

Answer 1

您通常会使用 MySQL 8+ 中提供的窗口函数来执行此操作。

select pabm.*
from (select pabm.*,
             row_number() over (partition by person_id order by interest_score desc) as seqnum
      from personAndBookinMapping pabm
     ) pabm
where seqnum <= 2;

在早期版本中，变量是最一致的方法:

select pabm.*
from (select pabm.*,
             (@rn := if(@p = person_id, @rn + 1,
                        if(@p := person_id, 1, 1)
                       )
             ) as rn
      from (select pabm.*
            from personAndBookinMapping pabm
            order by person_id, interest_score desc
           ) pabm cross join
           (select @p := -1, @rn := 0) params
     ) pabm
where rn <= 2;