本文介绍了用逗号分隔的列表从连接更新数据库表字段的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个名为 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?
我只做到了:
地区表
+------------+------+---------+
| 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 演示
这篇关于用逗号分隔的列表从连接更新数据库表字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!