sql - 左联接，行数有限

我有以下情况。我有三张桌子：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，但问题仍然存在。如何进行此查询？

最佳答案

你可以用两种不同的方法：
横向连接：

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;

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

关于sql - 左联接，行数有限，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49280275/