MS SQL Server多表查询：如何实现学生对应居住城市名逗号拼接输出

首先明确两张表的核心结构：

• 城市表：主键CityID，存储城市名称的字段CityName
• 学生关联表：存储学生姓名的字段StudentName，关联城市表的外键字段CityID

方案1：SQL Server 2017及更高版本（推荐） #

使用官方内置的STRING_AGG聚合函数实现同组字符串拼接，写法最简单高效：

SELECT
  学生表.StudentName,
  STRING_AGG(城市表.CityName, ',') AS 居住城市
FROM
  学生表
INNER JOIN 城市表 ON 学生表.CityID = 城市表.CityID
GROUP BY
  学生表.StudentName

方案2：兼容SQL Server 2016及更早版本 #

旧版本没有STRING_AGG函数，可以通过FOR XML PATH语法实现相同效果：

SELECT
  DISTINCT s1.StudentName,
  STUFF(
    (
      SELECT ',' + c.CityName
      FROM 学生表 s2
      INNER JOIN 城市表 c ON s2.CityID = c.CityID
      WHERE s2.StudentName = s1.StudentName
      FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'),
  1, 1, '') AS 居住城市
FROM 学生表 s1

执行以上任意一种符合你数据库版本的查询，即可得到学生姓名和对应所有居住城市逗号分隔的结果。

内容的提问来源于stack exchange，提问作者Nikolo