您可以使用相关的子查询:
select *
from ResultView rv1
where SubjectId || '-' || StudentId || '-' || LevelId in
(
select SubjectId || '-' || StudentId || '-' || LevelId
from ResultView rv2
where SubjectID = rv1.SubjectID
order by
total desc
limit 2
)
该查询通过串联三列来构造单列主键。如果您有真正的主键(如ResultViewID
),则可以用代替SubjectId || '-' ||StudentId || '-' || LevelId
。