SQL如何对逗号分隔存储的ID字段实现关联查询并拼接对应名称

核心逻辑 #

你需要完成三个步骤的处理：

• 拆分users表中SecondaryTeamIds字段的逗号分隔值，转为单行单ID的结构
• 拆分后的团队ID与teams表关联，获取对应的团队名称
• 按用户维度聚合，将多个副团队名称用逗号拼接为单个字段

不同数据库的具体实现 #

MySQL 8.0+ #

SELECT
  u.Name,
  t1.TeamName AS `Primary Team`,
  GROUP_CONCAT(t2.TeamName ORDER BY FIND_IN_SET(t2.TeamId, u.SecondaryTeamIds) SEPARATOR ', ') AS `Secondary Teams`
FROM users u
INNER JOIN teams t1 ON u.PrimaryTeamId = t1.TeamId
LEFT JOIN JSON_TABLE(
  CONCAT('["', REPLACE(u.SecondaryTeamIds, ',', '","'), '"]'),
  '$[*]' COLUMNS (team_id INT PATH '$')
) AS st ON 1=1
LEFT JOIN teams t2 ON st.team_id = t2.TeamId
GROUP BY u.Id, u.Name, t1.TeamName;

ORDER BY FIND_IN_SET部分是为了保证拼接后的团队顺序和原字符串的ID顺序一致，不需要可以去掉

PostgreSQL #

SELECT
  u.Name,
  t1.TeamName AS "Primary Team",
  STRING_AGG(t2.TeamName, ', ' ORDER BY ordinality) AS "Secondary Teams"
FROM users u
INNER JOIN teams t1 ON u.PrimaryTeamId = t1.TeamId
LEFT JOIN UNNEST(STRING_TO_ARRAY(u.SecondaryTeamIds, ',')) WITH ORDINALITY AS st(team_id, ordinality) ON 1=1
LEFT JOIN teams t2 ON st.team_id::INT = t2.TeamId
GROUP BY u.Id, u.Name, t1.TeamName;

SQL Server 2016+ #

SELECT
  u.Name,
  t1.TeamName AS [Primary Team],
  STRING_AGG(t2.TeamName, ', ') WITHIN GROUP (ORDER BY st.[ordinal]) AS [Secondary Teams]
FROM users u
INNER JOIN teams t1 ON u.PrimaryTeamId = t1.TeamId
OUTER APPLY STRING_SPLIT(u.SecondaryTeamIds, ',', 1) st
LEFT JOIN teams t2 ON st.value = t2.TeamId
GROUP BY u.Id, u.Name, t1.TeamName;

注意事项 #

• 如果你的数据库版本不支持上述拆分函数，可以自行实现自定义拆分函数完成字符串拆行逻辑
• 如果存在SecondaryTeamIds为空的用户，上述写法会返回NULL作为副团队字段值，你可以用COALESCE函数替换为空字符串或者其他默认值

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