sql-server - 加入额外表时的东西？

Question

见下表TABLE：

ID    FIELD_1
-------------
1     123
1     456
1     789
2     111

我想列出FIELD_1每个 ID 的所有 s，如下所示：

select [ID],
       stuff((
           select '; ' + [FIELD_1]
           from   [TABLE] t1
           where  t1.[ID] = t2.[ID]
           for    xml path ('')
       ),1,1, '') as [FIELDS]
from   [TABLE] t2

输出：

ID    FIELDS
------------
1     123; 456; 789
2     111

但现在; 每个都FIELD_1可以在另一个表中有多个条目（TABLE2）：

FIELD_1    FIELD_2
------------------
123        A
123        B
456        B
111        C
111        X
111        Z

我需要将这些添加到我的输出中，如下所示：

ID    FIELDS
------------
1     123 (A, B); 456 (B); 789
2     111 (C, X, Z)

所以我通过如下加入来尝试这个：

select [ID],
       stuff((
           select '; ' + [FIELD_1] + [FIELD_2]
           from   [TABLE] t1
           where  t1.[ID] = t2.[ID]
           for    xml path ('')
       ),1,1, '') as [FIELDS]
from   [TABLE] t2
left join [TABLE2] t3 on t2.[ID] = t3.[ID]

但这会为FIELD_1in的每次出现返回一行TABLE2。

此查询的正确方法是什么？我需要STUFF()在现有的STUFF()选择中使用吗？

标签： sql-servertsqlsql-server-2012

Answer 1

declare @t2 table (id int)
    insert into @t2 values (1),(2)

declare @t1 table (id int, field_1 varchar(55))
    insert into @t1 values (1,'123'), (1,'456'),(1,'789'),(2,'111')

declare @t3 table (field_1 varchar(55), field_2 varchar(55))
    insert into @t3 values ('123','A'), ('123','B'),('456','B'),('111','C'),('111','X'),('111','Y')

select  [ID],
        stuff(( select '; ' + [FIELD_1]+ISNULL(' ('+[FIELDS_2]+')','')
                from(
                        select id, [FIELD_1],
                               stuff((
                                   select ',' + t3.[FIELD_2]
                                   from   @t3 t3 
                                   where  t3.[FIELD_1] = t1.[FIELD_1]
                                   for    xml path ('')
                               ),1,1, '') as [FIELDS_2]
                        from   @t1 t1
                         where  t1.[ID] = t2.[ID])t           for    xml path ('')
                               ),1,1, '') as [FIELDS]
from   @t2 t2