如何使用concat或listagg聚合同列值实现两表关联获取对应row_id

你提出的聚合后关联的思路完全可行，核心是把同分组下的bb_id集合转为唯一的标识字符串，用该标识替代单个bb_id做关联键，就能避免重复值问题。

实现逻辑 #

1. 分别对两张表做分组聚合，将同个res_id（TABLE_A）、同个row_id（table_b）下的所有bb_id排序后拼接为单个字符串，作为分组的唯一特征标识。必须加排序逻辑，避免同一组内bb_id顺序不同导致拼接结果不一致，匹配失败。
2. 用公共字段pos_id + 拼接得到的分组标识做关联，即可得到正确的匹配关系。

示例SQL（MySQL语法） #

1. 聚合TABLE_A得到每个res_id对应的bb_id集合标识

SELECT
  pos_id,
  res_id,
  GROUP_CONCAT(DISTINCT bb_id ORDER BY bb_id ASC) AS bb_group_tag
FROM TABLE_A
GROUP BY pos_id, res_id

2. 聚合table_b得到每个row_id对应的bb_id集合标识

SELECT
  pos_id,
  row_id,
  GROUP_CONCAT(DISTINCT bb_id ORDER BY bb_id ASC) AS bb_group_tag
FROM table_b
GROUP BY pos_id, row_id

3. 关联两个聚合结果得到最终匹配关系

SELECT
  a.res_id,
  b.row_id
FROM
  (SELECT pos_id, res_id, GROUP_CONCAT(DISTINCT bb_id ORDER BY bb_id ASC) AS bb_group_tag FROM TABLE_A GROUP BY pos_id, res_id) a
INNER JOIN
  (SELECT pos_id, row_id, GROUP_CONCAT(DISTINCT bb_id ORDER BY bb_id ASC) AS bb_group_tag FROM table_b GROUP BY pos_id, row_id) b
ON a.pos_id = b.pos_id AND a.bb_group_tag = b.bb_group_tag

执行后结果完全符合预期：res_id 10000、10001对应row_id 1，res_id 10002、10003对应row_id 2。

其他数据库适配 #

替换对应字符串拼接聚合函数即可：

• PostgreSQL：使用STRING_AGG(DISTINCT bb_id::VARCHAR, ',' ORDER BY bb_id ASC)替代GROUP_CONCAT
• Hive/Spark SQL：使用CONCAT_WS(',', SORT_ARRAY(COLLECT_SET(bb_id)))替代GROUP_CONCAT

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