SQL Server多对多关系表规范化后查询拼接原始格式方案

场景说明 #

在SQL Server 15.x版本中做数据库规范化设计时，最初的未拆分教师表存储以下字段：

• TeacherId：主键
• FirstName：教师名
• LastName：教师姓
• Course：授课课程，逗号分隔存储多值
• GroupCode：所属组编码，逗号分隔存储多值

初始未拆分表样例数据：

按照三范式拆分后得到5张表：

• 3张实体主表：Teachers教师表、Courses课程表、GroupCodes组编码表
• 2张多对多关联中间表：TeacherCourse教师-课程关联表、TeacherGroup教师-组关联表

拆分后表结构 #

主表结构：

中间表结构：
TeacherCourse（教师-课程关联表）：

TeacherGroup（教师-组关联表）：

建表及测试数据脚本 #

CREATE TABLE [dbo].[Teachers]
(
    [TeacherId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [FirstName] [nchar](10) NOT NULL,
    [LastName] [nchar](10) NOT NULL
)
GO
CREATE TABLE [dbo].[Courses]
(
    [Course] [nchar](10) NOT NULL PRIMARY KEY
)
GO

CREATE TABLE [dbo].[GroupCodes]
(
    [GroupCode] [nchar](10) NOT NULL PRIMARY KEY
)
GO

CREATE TABLE [dbo].[TeacherCourse]
(
    [TeacherId] [int] NOT NULL,
    [Course] [nchar](10) NOT NULL,
    PRIMARY KEY (TeacherId, Course),
    CONSTRAINT FK_TeacherCourses FOREIGN KEY (TeacherId) REFERENCES Teachers(TeacherId),
    CONSTRAINT FK_TeachersCourse FOREIGN KEY (Course) REFERENCES Courses(Course)
)
GO

CREATE TABLE [dbo].[TeacherGroup]
(
    [TeacherId] [int] NOT NULL,
    [GroupCode] [nchar](10) NOT NULL,
    PRIMARY KEY (TeacherId, GroupCode),
    CONSTRAINT FK_TeacherGroups FOREIGN KEY (TeacherId) REFERENCES Teachers(TeacherId),
    CONSTRAINT FK_TeachersGroup FOREIGN KEY (GroupCode) REFERENCES GroupCodes(GroupCode)
)
GO

INSERT INTO Teachers(FirstName,LastName)
VALUES ('Smith','Jane'),('Smith','John')
GO

INSERT INTO Courses(Course)
VALUES ('AAA','BBB','CCC')
GO

INSERT INTO GroupCodes(GroupCode)
VALUES ('A1','A2','B1','B2')
GO

INSERT INTO TeacherCourse(TeacherId,Course)
VALUES ('1','AAA'),('1','BBB'),('2','BBB'),('2','CCC')
GO

INSERT INTO TeacherGroup(TeacherId,GroupCode)
VALUES ('1','A1'),('1','A2'),('1','B2'),('2','A2'),('2','B1'),('2','B2')
GO

查询需求 #

编写SQL返回和初始未拆分表格式一致的结果：每个教师对应单行记录，课程、组编码字段还原为逗号分隔格式，期望结果如下：

错误写法说明 #

最初直接写多表JOIN无法得到正确结果，错误SQL如下：

SELECT t.TeacherId AS TeacherId, t.FName AS FirstName, t.LName AS LastName, 
       c.Course AS Course, g.GroupCode AS GroupCode
FROM TeacherCourse tc, TeacherGroup tg
JOIN Teachers t ON tc.TeacherId=t.TeacherId
JOIN Courses c ON tc.Course=c.Course
JOIN Teachers t ON tg.TeacherId=t.TeacherId
JOIN GroupCodes g ON tg.GroupCode=g.GroupCode
ORDER  BY TeacherId

该写法存在三个问题：

• 混用隐式逗号连接和显式JOIN语法，逻辑混乱
• 重复关联Teachers表，会引发别名冲突报错
• 直接笛卡尔积关联两个多对多中间表，会产生大量重复数据，无法实现单教师单行的聚合效果

正确实现方案 #

SQL Server 2017以下版本可以通过STUFF+FOR XML PATH的方式实现分组后字符串拼接，最初调试通过的可用SQL如下：

SELECT t.TeacherId AS TeacherId, t.FirstName AS FirstName, t.LastName AS LastName, 
(SELECT STUFF((SELECT DISTINCT ', ' + LTRIM(RTRIM(tc.Course)) FROM TeacherCourse tc 
            INNER JOIN Courses c ON tc.Course = c.Course WHERE tc.TeacherId = t.TeacherId
            FOR XML PATH('')),1,1,(''))) AS Courses,
(SELECT STUFF((SELECT DISTINCT ', ' + LTRIM(RTRIM(tg.GroupCode)) FROM TeacherGroup tg 
            INNER JOIN GroupCodes g ON tg.GroupCode = g.GroupCode WHERE tg.TeacherId = t.TeacherId
            FOR XML PATH('')),1,1,(''))) AS Group_Codes
FROM TeacherCourse tc
JOIN TeacherGroup tg ON
    tc.TeacherId = tg.TeacherId
JOIN Teachers t ON 
    tc.TeacherId=t.TeacherId AND tg.TeacherId=t.TeacherId
JOIN Courses c ON 
    tc.Course=c.Course
JOIN GroupCodes g ON 
    tg.GroupCode=g.GroupCode
/* 可以在此处添加WHERE条件过滤特定结果，例如：
WHERE t.LastName = 'Jane'
*/
GROUP BY t.TeacherId, t.FirstName, t.LastName
ORDER  BY TeacherId

可以进一步简化逻辑，去掉外层多余的关联表，直接从Teachers主表出发做子查询拼接，减少不必要的表扫描开销，简化后写法：

SELECT 
    t.TeacherId,
    TRIM(t.FirstName) AS FirstName,
    TRIM(t.LastName) AS LastName,
    STUFF((
        SELECT ',' + TRIM(tc.Course)
        FROM TeacherCourse tc
        WHERE tc.TeacherId = t.TeacherId
        ORDER BY tc.Course
        FOR XML PATH('')
    ), 1, 1, '') AS Course,
    STUFF((
        SELECT ',' + TRIM(tg.GroupCode)
        FROM TeacherGroup tg
        WHERE tg.TeacherId = t.TeacherId
        ORDER BY tg.GroupCode
        FOR XML PATH('')
    ), 1, 1, '') AS GroupCode
FROM Teachers t
ORDER BY t.TeacherId

如果是SQL Server 2017及以上版本，还可以直接用内置的STRING_AGG函数实现拼接，写法更简洁：

SELECT 
    t.TeacherId,
    TRIM(t.FirstName) AS FirstName,
    TRIM(t.LastName) AS LastName,
    STRING_AGG(TRIM(tc.Course), ',') WITHIN GROUP (ORDER BY tc.Course) AS Course,
    STRING_AGG(TRIM(tg.GroupCode), ',') WITHIN GROUP (ORDER BY tg.GroupCode) AS GroupCode
FROM Teachers t
LEFT JOIN TeacherCourse tc ON t.TeacherId = tc.TeacherId
LEFT JOIN TeacherGroup tg ON t.TeacherId = tg.TeacherId
GROUP BY t.TeacherId, t.FirstName, t.LastName
ORDER BY t.TeacherId

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