将分号分隔的值替换为另一个表值

Question

我有两个表，Books和Authors。

桌子 Books

ID     Title     Authors
1      apple     1;2;3;
2      cancer    1;5;

桌子 Authors

ID     Name
1      John
2      Mike
3      Joe
4      Katy
5      Sara

我需要一个查询，结果是

ID     Title     Authors
1      apple     John;Mike;Joe
2      cancer    John;Sara

Answer 1

这个怎么样？但是，您必须确保Books表中的Authors列始终具有有效数据。

SELECT ID,
       Title,
       STUFF(CAST((SELECT ';' + aut.Name 
                     FROM Authors aut 
                    WHERE aut.ID IN 
                       (SELECT CAST(Split.a.value('.','VARCHAR(max)') AS int) As bid  
                          FROM  
                             ( SELECT CAST ('<M>' + REPLACE(LEFT(Authors, DATALENGTH(Authors)-1), ';', '</M><M>') + '</M>' AS XML) AS Data  
                             ) AS A CROSS APPLY Data.nodes ('/M') AS    Split(a))  
                      FOR XML PATH(''), TYPE))AS nvarchar(max)), 1,1,'')
 FROM Books