从每个组中选择前 n 条记录 SQLite

13 浏览
0 Comments

从每个组中选择前 n 条记录 SQLite

我尝试从数据库表结果中选择前两条记录，该结果看起来像这样：

学科编号 | 学生编号 | 等级编号 | 总分
------------------------------------------
 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行？

但是对于每个学科编号我得到了超过两个的结果

我做错了什么？

0

3 答案

0 Comments

问题出现的原因是需要在每个组中选择前n条记录，但是不知道如何实现。解决方法是使用关联子查询，并将结果按照指定的顺序排序和限制返回的记录数量。

具体的解决方法如下：

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`。如果要根据`levelid`选择结果，可以在外层查询中添加`where`子句。

详细的例子可以在SQL Fiddle中查看。

感谢答案，但是我得到了不同的结果。我上面贴出的例子是我问题的简化版本，可能因此有所差异。我将发布一个更详细的版本。我要选择的表实际上是通过一个共同的ID将两个表连接起来的视图。如果我想要根据`levelid`选择结果，在哪里放置`where`子句？

0
0 Comments

问题：如何在SQLite中选择每个组的前n条记录？

原因：在SQLite中，默认情况下没有直接的方法选择每个组的前n条记录。需要使用窗口函数来实现此功能。

解决方法：使用ROW_NUMBER窗口函数来选择每个组的前n条记录。以下是一个示例查询：

SELECT *
FROM (
    SELECT
      ROW_NUMBER() OVER (
        PARTITION BY "StudentID"
        ORDER BY "total" DESC
      ) AS "rnk",
      *
    FROM "mytable"
  ) sub
WHERE
  "sub"."rnk" <= 2
ORDER BY
  "sub"."StudentID" ASC,
  "sub"."total" DESC

上述查询中，首先使用ROW_NUMBER()函数为每个组的记录分配一个序号，并根据指定的排序规则进行排序。然后，外部查询选择序号小于等于2的记录，以获取每个组的前两条记录。最后，按照StudentID升序和total降序进行排序。

这种方法可以在SQLite 3.34和PostgreSQL 14.3中使用。

可以在GitHub上找到示例代码和更多信息：[GitHub upstream](https://github.com/cirosantilli/cirosantilli.github.io/blob/2ea910f407098ad3c7fac9f25af585f7e4cff4b1/nodejs/sequelize/raw/group_by_max_n.js)

0
0 Comments

在这段对话中，问题的提出者想要在每个分组中选择前n条记录。他们已经创建了一个名为"stack"的表，并插入了一些样本数据。问题的解决方法是使用嵌套查询和LIMIT子句来选择每个分组中的前n条记录。

解决方法如下：

- 如果要按照Levelid进行分组并选择每个组的前两条记录，可以使用以下查询：

SELECT *

FROM stack AS a

WHERE a.StudentID IN (

SELECT b.StudentID

FROM stack AS b

WHERE a.levelid = b.levelid

ORDER BY b.total DESC

LIMIT 2

);

这将返回按照Levelid分组的前两条记录。

- 如果要按照SubjectId进行分组并选择每个组的前两条记录，可以使用以下查询：

SELECT *

FROM stack AS a

WHERE a.StudentID IN (

SELECT b.StudentID

FROM stack AS b

WHERE a.subjectID = b.subjectID

ORDER BY b.total DESC

LIMIT 2

);

这将返回按照SubjectId分组的前两条记录。

然而，这种解决方法的缺点是执行时间较长，每10K条记录需要30秒。

0