如何用SQL查询关联逗号分隔值，获取任务所有者名称？

数据表结构 #

Task表 #

ID    Task_Name   Task_Owner
1      Create      1,3,4
2       Edit       2,1,3
3      Delete      2,1,4

Owner表 #

ID        Owner_Name
1           Mike
2           Ken
3           Lim
4           Nick

需求 #

编写SQL查询语句，将Task表中逗号分隔的Task_Owner（用户ID列表）转换为对应的所有者名称，输出格式如下：

ID    Task Name      Owners
1     Create         Mike, Lim, Nick
2     Edit           Ken, Mike, Lim
3     Delete         Ken, Mike, Nick

分数据库实现方案 #

MySQL 8.0+ #

利用FIND_IN_SET判断关联关系，结合GROUP_CONCAT聚合名称，同时保留原ID顺序：

SELECT 
    t.ID,
    t.Task_Name AS `Task Name`,
    GROUP_CONCAT(o.Owner_Name ORDER BY FIND_IN_SET(o.ID, t.Task_Owner) SEPARATOR ', ') AS Owners
FROM Task t
JOIN Owner o ON FIND_IN_SET(o.ID, t.Task_Owner) > 0
GROUP BY t.ID, t.Task_Name;

SQL Server #

通过LIKE匹配关联ID，用STRING_AGG聚合并按原顺序排序：

SELECT 
    t.ID,
    t.Task_Name AS [Task Name],
    STRING_AGG(o.Owner_Name, ', ') WITHIN GROUP (ORDER BY CHARINDEX(',' + CAST(o.ID AS VARCHAR) + ',', ',' + t.Task_Owner + ',')) AS Owners
FROM Task t
JOIN Owner o ON ',' + t.Task_Owner + ',' LIKE '%,' + CAST(o.ID AS VARCHAR) + ',%'
GROUP BY t.ID, t.Task_Name;

PostgreSQL #

用STRING_TO_ARRAY拆分ID数组，关联后通过STRING_AGG聚合并保持原顺序：

SELECT 
    t.ID,
    t.Task_Name AS "Task Name",
    STRING_AGG(o.Owner_Name, ', ' ORDER BY POSITION(',' || o.ID || ',' IN ',' || t.Task_Owner || ',')) AS Owners
FROM Task t
JOIN Owner o ON o.ID = ANY(STRING_TO_ARRAY(t.Task_Owner, ',')::INT[])
GROUP BY t.ID, t.Task_Name;

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