如何在SQL中查询每个艺术家的最高播放量专辑

问题场景 #

我有一个包含ARTIST和SONG表的数据库，每首歌曲都有播放量、关联的专辑以及所属的artist_id。需要获取每位艺术家旗下所有歌曲播放量总和最高的专辑。

表结构及示例数据 #

Artist表

Song表

期望结果 #

原SQL问题分析 #

我尝试的SQL语句返回了每个艺术家的所有专辑，不符合预期：

SELECT a.name, s.name_alb
  FROM artist a
  JOIN song s
    ON (a.artist_id = s.artist_id)
 GROUP BY a.artist_id, a.name, s.name_alb
HAVING SUM(s.n_reproductions) = (SELECT MAX(SUM(s1.n_reproductions))
                                   FROM song s1
                                  WHERE s1.artist_id = a.artist_id
                                    AND s1.name_alb = s.name_alb
                                  GROUP BY s1.artist_id, s1.name_alb);

问题出在子查询中的s1.name_alb = s.name_alb条件，这会让子查询只计算当前专辑的播放量总和，然后取这个值的最大值（也就是它自己），导致HAVING条件永远成立，所以返回所有专辑。

正确解决方案 #

方法1：使用窗口函数（推荐） #

利用ROW_NUMBER()窗口函数按艺术家分组，对专辑播放量总和降序排序，取每个组的第一行：

WITH album_reproductions AS (
    SELECT 
        a.name AS artist_name,
        s.name_album,
        SUM(s.n_reproductions) AS total_reproductions,
        ROW_NUMBER() OVER (PARTITION BY a.artist_id ORDER BY SUM(s.n_reproductions) DESC) AS rn
    FROM artist a
    JOIN song s ON a.artist_id = s.artist_id
    GROUP BY a.artist_id, a.name, s.name_album
)
SELECT artist_name, name_album
FROM album_reproductions
WHERE rn = 1;

如果存在同一艺术家多个专辑播放量总和相同且都是最高的情况，可以把ROW_NUMBER()换成RANK()，这样会返回所有并列最高的专辑。

方法2：使用子查询嵌套 #

先计算每个艺术家每个专辑的播放量总和，再找出每个艺术家的最高播放量，最后关联得到对应专辑：

SELECT a.name, album_totals.name_album
FROM artist a
JOIN (
    -- 计算每个艺术家每个专辑的总播放量
    SELECT 
        artist_id,
        name_album,
        SUM(n_reproductions) AS total_reproductions
    FROM song
    GROUP BY artist_id, name_album
) AS album_totals ON a.artist_id = album_totals.artist_id
JOIN (
    -- 计算每个艺术家的最高专辑播放量
    SELECT 
        artist_id,
        MAX(total_reproductions) AS max_total
    FROM (
        SELECT 
            artist_id,
            SUM(n_reproductions) AS total_reproductions
        FROM song
        GROUP BY artist_id, name_album
    ) AS temp
    GROUP BY artist_id
) AS max_totals ON album_totals.artist_id = max_totals.artist_id 
    AND album_totals.total_reproductions = max_totals.max_total;

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