PostgreSQL中按流派分组获取最新上映电影的查询方法

假设你的电影表名为movies，包含核心字段：genre（流派）、title（电影名称）、release_date（上映日期，日期类型）。以下是两种高效的实现方式：

方法一：使用窗口函数ROW_NUMBER() #

这种方法通用性强，适配复杂排序需求：

SELECT genre, title, release_date
FROM (
    SELECT 
        genre,
        title,
        release_date,
        ROW_NUMBER() OVER (PARTITION BY genre ORDER BY release_date DESC) AS rn
    FROM movies
) ranked
WHERE rn = 1;

• PARTITION BY genre：按流派分组
• ORDER BY release_date DESC：在每个流派内按上映日期倒序排列，最新影片排首位
• WHERE rn = 1：筛选每个分组里排名第一的记录，即该流派最新上映的电影

方法二：使用PostgreSQL特有的DISTINCT ON #

这种写法更简洁，适合简单场景：

SELECT DISTINCT ON (genre) genre, title, release_date
FROM movies
ORDER BY genre, release_date DESC;

• DISTINCT ON (genre)：确保每个流派仅返回一条记录
• ORDER BY genre, release_date DESC：先按流派分组，再在组内按上映日期倒序，取第一条即为该流派最新电影

注意事项 #

• 如果同一流派有多部电影在同一天上映，上述两种方法仅返回其中一条。若需返回所有同日期的最新电影，将ROW_NUMBER()替换为RANK()即可。
• 确保release_date字段为有效日期类型，避免排序逻辑出错。

内容的提问来源于stack exchange，提问作者Joe Lee