我有两个这样的 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行?
发布评论