SQL JOIN关联时如何为每个podcast仅从categories表取1条匹配记录

问题原因 #

你遇到的重复是因为一个podcast_id在categories表对应多条记录，关联后会把同一条评论复制成和分类数量相同的条数。用GROUP BY podcasts.title行不通的原因是分组后会把同一个播客的多条评论合并，无法保留全量评论记录。

解决方案 #

核心思路是提前对categories表做去重处理，保证每个podcast_id仅对应一条分类记录后，再和其他表关联，不会影响评论数据的完整性。

方案1：聚合函数提前取唯一分类 #

适合无特殊分类选取规则的场景，直接取每个播客的任意一条分类即可，示例用MAX取字母序最大的分类，你也可以换成MIN：

SELECT 
  podcasts.title AS podcast,
  c.category,
  reviews.review_title AS review,
  reviews.rating,
  reviews.author_id
FROM podcasts
LEFT OUTER JOIN reviews 
  ON reviews.podcast_id = podcasts.podcast_id
LEFT JOIN (
  SELECT podcast_id, MAX(category) AS category 
  FROM categories 
  GROUP BY podcast_id
) AS c 
  ON c.podcast_id = podcasts.podcast_id

方案2：窗口函数自定义分类选取规则 #

适合有明确分类选取优先级的场景，你可以修改窗口函数内的ORDER BY规则调整优先取哪条分类：

SELECT 
  podcasts.title AS podcast,
  c.category,
  reviews.review_title AS review,
  reviews.rating,
  reviews.author_id
FROM podcasts
LEFT OUTER JOIN reviews 
  ON reviews.podcast_id = podcasts.podcast_id
LEFT JOIN (
  SELECT 
    podcast_id, 
    category,
    ROW_NUMBER() OVER (PARTITION BY podcast_id ORDER BY category) AS rn
  FROM categories
) AS c 
  ON c.podcast_id = podcasts.podcast_id 
  AND c.rn = 1

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