从每个组中选择前n条记录 [英] select top n record from each group sqlite

问题描述

我正在尝试从看起来像这样的数据库表结果中选择前2条记录

I am trying to select top 2 records from a database table result that looks like this

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  

但是一些主题，例如失踪的地方，我什至尝试了以下链接的建议

but some subjects like where missing, i even tried the suggestiong frm the following link

如何选择每行的前N行组吗?

但是我得到的每个主题ID都超过了两个

but i get more that two for each subjectid

我在做什么错了?

推荐答案

您可以使用相关子查询:

You could use a correlated subquery:

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.

This query constructs a single-column primary key by concatenating three columns. If you have a real primary key (like ResultViewID) you can substitute that for SubjectId || '-' || StudentId || '-' || LevelId.

SQL Fiddle中的示例.

这篇关于从每个组中选择前n条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！