JOIN查询行重复，如何消除features_id与tag_list字段重复值？

问题场景 #

现有history_banners和history_features两张表，需通过id_banner关联获取数据。已知同一id_banner在两张表中的行数完全一致（如id_banner=51在两表中各对应3行），但执行原查询后，features_id和tag_list字段出现重复值。

原查询SQL：

SELECT 
  bs.id, bs.title_banner, bs.text_banner, bs.url_banner, bs.banner_state, bs.created_at, bs.updated_at, t.tag_list, t.features_id 
FROM 
  history_banners bs 
JOIN 
  (SELECT DISTINCT ON (id_banner) id_banner, tag_list, features_id FROM history_features WHERE id_banner = 51) t 
ON 
  bs.id_banner = t.id_banner;

问题原因 #

原查询中使用DISTINCT ON (id_banner)会从history_features表中仅返回id_banner=51对应的任意一行数据，再与history_banners中该id_banner下的所有行做关联，导致这一行的features_id和tag_list被重复匹配到每一条banner记录上，最终出现字段重复。

解决方案 #

由于两张表同一id_banner下的行数一致，可通过窗口函数生成行号的方式，建立行与行之间的一一对应关系，避免重复匹配：

SELECT 
  bs.id, bs.title_banner, bs.text_banner, bs.url_banner, bs.banner_state, bs.created_at, bs.updated_at, t.tag_list, t.features_id 
FROM 
  (
    -- 给history_banners中同一id_banner的行生成行号
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id_banner ORDER BY id) AS row_num
    FROM history_banners 
    WHERE id_banner = 51
  ) bs
JOIN 
  (
    -- 给history_features中同一id_banner的行生成行号
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id_banner ORDER BY id) AS row_num
    FROM history_features 
    WHERE id_banner = 51
  ) t 
ON 
  bs.id_banner = t.id_banner AND bs.row_num = t.row_num;

说明 #

• ROW_NUMBER() OVER (PARTITION BY id_banner ORDER BY id)：按id_banner分组，每组内按id排序生成连续行号，确保两表中同一id_banner下的行按相同规则排序后一一对应。
• 若两张表之间存在其他直接关联字段（如history_banners.id对应history_features.banner_id），直接使用该字段关联会更高效，无需行号。

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