作者:情系初冬_883 | 来源:互联网 | 2023-05-25 12:06
我试图从数据库表结果中选择前2个记录,如下所示
SubjectId | StudentId | Levelid | total
------------------------------------------
1 | 1 | 1 | 89
1 | 2 | 1 | 77
1 | 3 | 1 | 61
2 | 4 | 1 | 60
2 | 5 | 1 | 55
2 | 6 | 1 | 45
我试过这个查询
SELECT rv.subjectid,
rv.total,
rv.Studentid,
rv.levelid
FROM ResultView rv
LEFT JOIN ResultView rv2
ON ( rv.subjectid = rv2.subjectid
AND
rv.total <= rv2.total )
GROUP BY rv.subjectid,
rv.total,
rv.Studentid
HAVING COUNT( * ) <= 2
order by rv.subjectid desc
但是有些科目在哪里失踪,我甚至尝试了以下链接的建议
如何选择每组的前N行?
但是对于每个主观,我得到的更多
我究竟做错了什么?
1> Andomar..:
您可以使用相关子查询:
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
.
SQL Fiddle的例子.