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

update a database table field with comma separated list from join(用逗号分隔的列表从联接更新数据库表字段)

问题描述

我有两个名为 Districts 和 Schools 的表.Districts 表包含一个名为 Schools 的列.

I have two tables named Districts and Schools. The Districts table contains a column named Schools.

我需要从对应的 Schools 表中填充 Districts 表的 Schools 列，以便 中的每一行Districts 表有一个逗号分隔的Schools 表中的学校名称值列表.

I need to populate the Schools column of the Districts table from the corresponding Schools table, so that each row in the Districts table has a comma separated list of values of school names from the Schools table.

我该怎么做?我应该使用 UPDATE 查询还是存储过程?

How can I do this? Should I use an UPDATE query or a stored procedure?

我只知道:

SQL 小提琴

地区表

+------------+------+---------+
| 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      |
+------------+------+---------+

推荐答案

借助 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 演示

这篇关于用逗号分隔的列表从联接更新数据库表字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！