如何在SQLITE中获取每个对象的“前10”个数字？

Question

我有一个SQLite表，其中有author、book、release_date和seller列。

现在，我需要找到一种方法，用最新的release_date仅获取每个作者的前10本书。

如果可能的话，我还需要卖家，这是最经常出现在最新发布的前10名的书籍每个作者。结果应该是一个简单的表，只有作者和卖家。

这个问题真的快把我逼疯了。在一个SQLite查询中至少有一个部分是可能的吗？

Answer 1

2018年9月发布的SQLite v3.25增加了window functions。

您可以使用以下命令按日期计算每个作者的图书排名：

CREATE TABLE books
(
  author varchar(10),
  title varchar(10),
  release date
);
INSERT INTO books VALUES 
('aaa','ta1','2018-01-01'),
('aaa','ta2','2018-02-01'),
('aaa','ta3','2018-03-01'),
('aaa','ta4','2018-05-01'),
('bbb','tb1','2018-05-01'),
('bbb','tb2','2018-06-01')
;

SELECT
    author,
    title,
    release, 
    row_number() OVER (partition by author ORDER BY release desc) AS row_number 
FROM books

如果行是按发布日期排序的，则函数row_number() OVER (partition by author ORDER BY release desc) AS row_number会计算每个作者的行号。

这会产生以下结果：

author  title   release     row_number
aaa     ta4     2018-05-01  1
aaa     ta3     2018-03-01  2
aaa     ta2     2018-02-01  3
aaa     ta1     2018-01-01  4
bbb     tb2     2018-06-01  1
bbb     tb1     2018-05-01  2

一旦你有了行号，你就可以用一个简单的WHERE row_number <= N过滤前N个项目，例如每个作者的最后两本书：

select * from (
SELECT
    author,
    title,
    release, 
    row_number() OVER (partition by author ORDER BY release desc) AS row_number 
FROM books )
where row_number<=2

这将返回：

author  title   release     row_number
aaa    ta4      2018-05-01  1
aaa    ta3      2018-03-01  2
bbb    tb2      2018-06-01  1
bbb    tb1      2018-05-01  2