T-SQL 有关问题

T-SQL 问题
RT 这是源表dbo.abc
iddbnameaccount
1db-1sa
2db-1cn\A
3db-1cn\B
4db-2sa
5db-2cn\C
6db-3cn\D
7db-3cn\E
8db-4admin

我的代码及其想要的结果：

SQL code

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 t1LEFT JOIN dbo.abc t2ON t1.dbname=t2.dbname AND t1.account<>t2.account) t3LEFT JOIN dbo.abc t4ON t3.dbname_1=t4.dbname AND t3.account_1<>t4.account AND t3.account_2<>t4.accountGROUP BY t3.dbname_1

结果：
数据库名对应账号
db-1sa;cn\B;cn\A
db-2sa;cn\C;
db-3cn\E;cn\D;
db-4admin;;

=========================分割线================
显然我的代码没有扩展性。但我想不出好的方法来实现这个功能了。而且我想用一段t-sql，而不用存储过程或者函数（可以过程实现）实现。

有高手请教一下吗？

[解决办法]

SQL code

select dbname as  [数据库名], [对应账号]=stuff((select ';'+account from abc where dbname=a.dbname for xml path('')),1,1,'')from abc agroup by id 

 

查看更多 下一篇