如何选择每组的前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.
相关文章