如何选择每组的前N行?

本文介绍了如何选择每组的前N行?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两个这样的 SQLite 表:

I have two SQLite tables like this:

AuthorId | AuthorName ---------------------- 1 | Alice 2 | Bob 3 | Carol ... | .... BookId | AuthorId | Title ---------------------------------- 1 | 1 | aaa1 2 | 1 | aaa2 3 | 1 | aaa3 4 | 2 | ddd1 5 | 2 | ddd2 ... | ... | ... 19 | 3 | fff1 20 | 3 | fff2 21 | 3 | fff3 22 | 3 | fff4

我想做一个 SELECT 查询，它将返回每个 AuthorId 的前 N(例如两)行，按标题排序(选择每个作者的前两本书").

I want to make a SELECT query that will return the first N (e.g. two) rows for each AuthorId, ordering by Title ("Select the first two books of each author").

示例输出:

BookId | AuthorId | AuthorName | Title ------------------------------------------ 1 | 1 | Alice | aaa1 2 | 1 | Alice | aaa1 4 | 2 | Bob | ddd1 5 | 2 | Bob | ddd2 19 | 3 | Carol | fff1 20 | 3 | Carol | fff2

如何构建此查询?

(是的，我找到了一个类似的主题，我知道如何只返回一行(第一行或顶部).问题在于两者).

(Yes, I found a similar topic, and I know how to return only one row (first or top). The problem is with the two).

推荐答案

您可以使用相关子查询进行计数:

You can do the counting using a correlated subquery:

SELECT b.BookId, a.AuthorId, a.AuthorName, b.Title FROM Author a join Book b on a.AuthorId = b.AuthorId where (select count(*) from book b2 where b2.bookId <= b.BookId and b2.AuthorId = b.AuthorId ) <= 2;

对于小型数据库，这应该没问题.如果您在 Book(AuthorId, BookId) 上创建复合索引，那么这将有助于查询.

For a small database this should be fine. If you create a composite index on Book(AuthorId, BookId) then that will help the query.