Sqlite查询:每个Podcast的最新五集

Question

我的应用程序将数百个播客集存储在一个SQLite表中。我正在寻找一个查询，它为每个播客返回最近五集( podcast存储在不同的表中)。

相关的表列是

• media_id =主键(插曲的唯一标识符)
• publication_date =可以用来查找最近的几集
• episode_remote_podcast_feed_location =定义与播客的关系

以下是我尝试过的：

SELECT *
FROM episodes a
WHERE a.media_id IN (
    SELECT b.media_id
    FROM episodes b 
    WHERE a.episode_remote_podcast_feed_location = b.episode_remote_podcast_feed_location
    ORDER BY b.episode_remote_podcast_feed_location, b.publication_date DESC
    LIMIT 5
) 
ORDER BY a.episode_remote_podcast_feed_location, a.publication_date

上面的查询总是返回五集(总计)-不管我有多少播客。至少这些插曲都属于同一个播客。(对于episode_remote_podcast_feed_location来说，剧集的价值是一样的。)

需要解决方案：我需要一个查询，为每个播客返回五集(最近的一集)。因此，如果应用程序只存储了一个播客，则返回五集。如果应用程序存储了三个播客，则返回15集: podcast b 5集，podcast c 5集。

附加信息：需要能够在应用程序中使用查询。与安卓捆绑的SQLite版本是不是最新的。一些SQLite特性，例如窗口函数，在Android上是不受支持的(参见：https://stackoverflow.com/a/55069907/14326132)。

Answer 1

您可以在子查询中使用row_number()枚举每个播客的情节，然后在外部查询中的每个播客中筛选前5个：

select *
from (
    select e.*, 
        row_number() over(
            partition by episode_remote_podcast_feed_location 
            order by  publication_date desc
        ) rn
    from episodes e
) e
where rn <= 5

在不支持窗口函数的SQLite版本中，另一种选择是关联子查询：

select *
from episodes e
where (
    select count(*) 
    from episodes e1 
    where 
        e1.episode_remote_podcast_feed_location = e.episode_remote_podcast_feed_location 
        and e1.publication_date  >= e.publication_date 
) <= 5