sql - 左连接有限数量的行

Question

我有以下情况。我有 3 个表:artists、songs 和 stats。我想创建一个查询，显示每个艺术家的最新 10 首歌曲被播放了多少次。目前我有以下内容:

SELECT artists.id, COALESCE(SUM(stats.plays), 0)

FROM artists

LEFT JOIN (
    SELECT id

    FROM songs as inner_songs
    WHERE inner_songs.artist_id = artists.id

    ORDER BY published_at DESC

    LIMIT 10
) AS songs

LEFT JOIN stats
ON stats.song_id = songs.id

GROUP BY artists.id

我收到以下错误:

HINT:  There is an entry for table "artsis", but it cannot be referenced from this part of the query.

现在我知道我不能在 de left join 中使用 artists.id，但问题仍然存在。我该如何执行此查询？

Answer 1

您可以通过两种不同的方式做到这一点:

横向连接:

SELECT artists.id, COALESCE(SUM(stats.plays), 0)
FROM artists
  LEFT JOIN LATERAL (
      SELECT id, artist_id
      FROM songs as inner_songs
      WHERE artist_id = artists.id
      ORDER BY published_at DESC
      LIMIT 10
  ) AS songs ON songs.artist_id = artists.id
  LEFT JOIN stats ON stats.song_id = songs.id
GROUP BY artists.id;

或者您可以在派生表中使用窗口函数:

SELECT artists.id, COALESCE(SUM(stats.plays), 0)
FROM artists
  LEFT JOIN (
      SELECT id, 
             artist_id, 
             row_number() over (partition by artist_id order by published_at) as rn
      FROM songs
  ) AS songs ON songs.artist_id = artists.id AND rn <= 10
  LEFT JOIN stats ON stats.song_id = songs.id
GROUP BY artists.id;

横向连接的解决方案很可能更快。