SQL School 作业，写一个 SELECT 返回

Question

编写一个 SELECT，它返回每个乐队的第五张专辑的名称。如果乐队没有第五张专辑，那应该什么也没有。如果一个乐队在一年内有更多专辑，则让按专辑名称的字典排列适用。结果应包括乐队名称和专辑名称，按照乐队名称升序(从 A 到 Z)按字典顺序排列。



我的解决方案不正确，因为我无法用 NULL 值填充第五张专辑。

SELECT brand_name, album_name
FROM (
    SELECT b.name AS brand_name, a.name AS album_name,
        ROW_NUMBER() OVER(PARTITION BY b.name ORDER BY a.release_year) AS rank
    FROM bands AS b
    JOIN albums AS a ON a.band_id = b.id
  ) tmp
WHERE rank = 5

我的结果:

正确结果:

Answer 1

您应该在子查询中对专辑进行排名，然后 left join在外部查询中:

SELECT b.name brand_name, a.name album_name
FROM bands b
LEFT JOIN (
    SELECT 
        band_id, 
        name,
        ROW_NUMBER() OVER(PARTITION BY band_id ORDER BY release_year) AS rn
    FROM albums
) a ON a.band_id = b.id AND a.rn = 5