22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT
t3.dbname_1 AS 数据库名,
MAX(ISNULL(t3.account_1,'')+';'+ISNULL(t3.account_2,'')+';'+ISNULL(t4.account,'')) AS 对应账号
FROM
(SELECT t1.id AS id_1,t1.dbname AS dbname_1,t1.account AS account_1
,t2.id AS id_2,t2.dbname AS dbname_2,t2.account AS account_2
FROM dbo.abc t1
LEFT JOIN dbo.abc t2
ON t1.dbname=t2.dbname AND t1.account<>t2.account) t3
LEFT JOIN dbo.abc t4
ON t3.dbname_1=t4.dbname AND t3.account_1<>t4.account AND t3.account_2<>t4.account
GROUP BY t3.dbname_1
select dbname as [数据库名],
[对应账号]=stuff((select ';'+account from abc where dbname=a.dbname for xml path('')),1,1,'')
from abc a
group by id