更新时间:2023-01-31 16:19:10
借助 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