sql 2005 连接结果 [英] sql 2005 join results
问题描述
使用 Microsoft SQL Server 2005:
Using Microsoft SQL Server 2005:
我有一个表测试":
1 11 测试 1
2 11 test2
3 11 test3
4 12 测试4
有没有办法查询这个,并返回按link_id"分组的结果,并加入名称?
Is there a way to query this, and return the results grouped by "link_id", with the names joined?
EG,
SELECT link_id, name FROM test WHERE ?????????
SELECT link_id, name FROM test WHERE ???????
结果:
11 test1、test2、test3
11 test1, test2, test3
12 test4
推荐答案
黑盒方法是使用 CROSS APPLY 和 FOR XML PATH:
The blackbox way of doing this is with a CROSS APPLY and FOR XML PATH:
declare @t table (id int, link_id int, name varchar(max))
insert into @t select 1, 11, 'test1'
union all select 2, 11, 'test2'
union all select 3, 11, 'test3'
union all select 4, 12, 'test4'
select b.link_id, d.link_names
from (
select distinct link_id
from @t a
) b
cross apply (
select name + ', ' as [text()]
from @t c
where b.link_id = c.link_id
for xml path('')
) d (link_names)
对于每一行,一个 CROSS APPLY 执行应用的子查询.在这种情况下,子查询被调用两次,分别是 link_id 11 和 12.然后子查询滥用 FOR XML 运算符将字符串相加.
For each row, a CROSS APPLY executes the applied subquery. In this case, the subquery is called twice, for link_id 11 and 12. The subquery then abuses the FOR XML operator to add the strings together.
如果你运行查询,它会打印:
If you run the query, it will print:
11 test1, test2, test3,
12 test4,
这篇关于sql 2005 连接结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!