SQL分组查询：如何显示每张专辑最长歌曲的名称等信息

问题背景 #

现有三张关联关系表：

• bands (id, name)：乐队表，主键id
• albums (id, name, release_year, band_id)：专辑表，主键id，外键band_id关联乐队表
• songs (id, name, length, album_id)：歌曲表，主键id，外键album_id关联专辑表

需求是查询每张专辑的最长歌曲，需显示专辑名、发行年份、歌曲时长、歌曲名称。原查询能获取前三项，但无法直接加入歌曲名称（因歌曲名称既不在GROUP子句，也未使用聚合函数）。

可行解决方案 #

方法一：子查询匹配最长时长 #

先通过子查询获取每张专辑的最长歌曲时长，再关联专辑和歌曲表，匹配专辑ID与时长，从而得到对应歌曲名称：

SELECT
    a.name AS '专辑名',
    a.release_year AS '发行年份',
    s.length AS '歌曲时长',
    s.name AS '歌曲名称'
FROM albums a
JOIN songs s ON a.id = s.album_id
WHERE (s.album_id, s.length) IN (
    SELECT album_id, MAX(length)
    FROM songs
    GROUP BY album_id
);

方法二：窗口函数排名（适用于支持窗口函数的数据库） #

利用RANK()或ROW_NUMBER()窗口函数，按专辑分组后对歌曲时长降序排名，筛选排名第一的记录：

SELECT
    专辑名,
    发行年份,
    歌曲时长,
    歌曲名称
FROM (
    SELECT
        a.name AS '专辑名',
        a.release_year AS '发行年份',
        s.length AS '歌曲时长',
        s.name AS '歌曲名称',
        -- 按专辑分组，时长降序排名，同最长时长的歌曲会并列第一
        RANK() OVER (PARTITION BY a.id ORDER BY s.length DESC) AS song_rank
    FROM albums a
    JOIN songs s ON a.id = s.album_id
) ranked_songs
WHERE song_rank = 1;

• 若专辑存在多首时长相同的最长歌曲，RANK()会返回所有符合条件的记录；若只需返回其中一首，可替换为ROW_NUMBER()。

原查询的问题说明 #

原查询通过albums.name, albums.release_year分组，只能返回分组级别的聚合结果（如最长时长）。歌曲名称属于分组内的非聚合字段，数据库无法确定要返回该分组下的哪一条歌曲记录，因此不能直接加入SELECT子句。

内容的提问来源于stack exchange，提问作者Veselin Dimov