如何选择每个组的前N行? [英] How to select the first N rows of each group?

问题描述

我有两个这样的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.

这篇关于如何选择每个组的前N行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！