用于查找按 id 排序和分组的 SQL 查询

Question

我有一个名为personAndBookinMapping的表 列是：映射标识 person_id book_id intreset_score book_name

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

mapping_id person_id book_id intreset_score book_name在此处输入链接说明

Answer 1

您通常使用Windows函数执行此操作，这些函数在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;