使用连接的逗号分隔列表更新数据库表字段

Question

我有两个名为 Districts 和 Schools 的表格。 Districts 表格中包含一个名为 Schools 的列。

我需要从对应的 Schools 表格填充 Districts 表格的 Schools 列，这样每一行 Districts 表格都有一个由逗号分隔的学校名称列表来自于 Schools 表格。

我该如何做？我应该使用 UPDATE 查询还是存储过程？

我只完成了这一步：

SQL Fiddle

Districts Table

+------------+------+---------+
| DistrictId | Name | Schools |
+------------+------+---------+
|          1 | a    |         |
|          2 | b    |         |
|          3 | c    |         |
|          4 | d    |         |
+------------+------+---------+

学校表

+----------+------------+------------+
| SchoolId | SchoolName | DistrictId |
+----------+------------+------------+
|        1 | s1         |          1 |
|        2 | s2         |          1 |
|        3 | s3         |          2 |
|        4 | s4         |          2 |
|        5 | s5         |          4 |
+----------+------------+------------+

输出需要的方式

+------------+------+---------+
| DistrictId | Name | Schools |
+------------+------+---------+
|          1 | a    | s1,s2   |
|          2 | b    | s3,s4   |
|          3 | c    |         |
|          4 | d    | s5      |
+------------+------+---------+

- vinayan

Answer 1

通过使用FOR XML PATH和STUFF将值合并，您可以轻松地使用所需结果更新表District。

UPDATE  a
SET     a.Schools = b.SchoolList
FROM    Districts a
        INNER JOIN
        (
            SELECT  DistrictId,
                    STUFF((SELECT ', ' + SchoolName
                            FROM Schools
                            WHERE DistrictId = a.DistrictId
                            FOR XML PATH (''))
                        , 1, 1, '')  AS SchoolList
            FROM    Districts AS a
            GROUP   BY DistrictId
        ) b ON A.DistrictId = b.DistrictId
WHERE   b.SchoolList IS NOT NULL

• SQLFiddle Demo

- John Woo

---

请使用STUFF(..., 1, 2, '')代替，因为分隔符', '是2个字符 - 这将防止列表有前导空格。 - ovinophile