sql - 棘手的 T-SQL 查询。连接主/子表之间一列的多个值

Question

我有如下三个表:

主表

+----------+-------------+
| MasterId | MasterName  |
+----------+-------------+
| 1        | Master 1    |
| 2        | Master 2    |
| 3        | Master 3    |
| 4        | Master 4    |
+----------+-------------+

子表

+----------+-------------+
| ChildId | ChildName    |
+----------+-------------+
| 1        | Child 1     |
| 2        | Child 2     |
| 3        | Child 3     |
| 4        | Child 4     |
+----------+-------------+

链接表

+----------+-----------------------+
| Id       | MasterId    | ChldId  |
+----------+-----------------------+
| 1        |  1          | 1       | 
| 2        |  2          | 1       | 
| 3        |  3          | 2       | 
| 4        |  4          | 3       | 
+----------+-----------------------+

一个 child 可以与多个主人链接，LinkTable 包含这个细节。我想要一个查询来选择以下内容:

1, 'Child 1', 'Master 1, Master 2', '1,2'
2, 'Child 2', 'Master 2', '2'
3, 'Child 3', 'Master 3', '3'

是否可以使用 COALESCE、STUFF、递归 CTE 等，不使用循环或调用其他函数？

Answer 1

要连接字符串，您可以使用此方法:How to concatenate all strings from a certain column for each group

测试数据:

declare @masterTable table(MasterId int identity, MasterName varchar(max))
insert @masterTable (MasterName) values('m1'), ('m2'), ('m3'), ('m4')

declare @childrenTable table(ChildId int identity, ChildName varchar(max))
insert @childrenTable (ChildName) values('c1'), ('c2'), ('c3'), ('c4')

declare @LinkTable table(MasterId1 int, MasterId2 int, ChildId int)
insert @LinkTable values(1,1,1), (2,2,1), (3,3,2), (4,4,3)

查询:

select t.*
from
(
    select c.ChildId, c.ChildName

        , STUFF((
            select ', ' + m.MasterName
            from
            (
                select l.MasterId1
                from @LinkTable l
                where l.ChildId = c.ChildId

                union

                select l.MasterId2
                from @LinkTable l
                where l.ChildId = c.ChildId
            )t
            join @masterTable m on m.MasterId = t.MasterId1
            for xml path(''), type
        ).value('.', 'varchar(max)'), 1, 2, '') [names]

        , STUFF((
            select ', ' + cast(t.MasterId1 as varchar(max))
            from
            (
                select l.MasterId1
                from @LinkTable l
                where l.ChildId = c.ChildId

                union

                select l.MasterId2
                from @LinkTable l
                where l.ChildId = c.ChildId
            )t
            for xml path(''), type
        ).value('.', 'varchar(max)'), 1, 2, '') [ids]
    from @childrenTable c
)t
where t.ids is not null

---

输出:

----------- --- -------- ------
1           c1  m1, m2   1, 2
2           c2  m3       3
3           c3  m4       4