MySQL:
UserId:"111";
HandleUserID:"111,222,333,444";
SELECT
t1.*
,(SELECT GROUP_CONCAT(st1.UserName SEPARATOR ',') FROM Table_User st1 WHERE FIND_IN_SET(st1.UserId,t1.HandleUserID)) AS HandleUserNames
FROM T_BDMS_Project t1
还可以使用CONCAT(str1,str2,…)与GROUP_CONCAT()连用返回连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。
比如:GROUP_CONCAT(CONCAT(st1.UserId,',',st1.UserName) SEPARATOR '|')
SqlServer:
UserId:"666";
HandleUserID:"666,888,222,444";
ApprovalUserID:"999,555,666,777";
SELECT T1.*
,STUFF((SELECT ','+UserName FROM Table_User WHERE CHARINDEX(UserID,T1.HandleUserID)>0 FOR XML PATH('')),1,1,'') AS UserNames
,ISNULL(STUFF((SELECT ','+UserName FROM Table_User WHERE CHARINDEX(UserID,T1.ApprovalUserID)>0 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''),'') AS ApprovalUserNames
FROM Table_Project T1