SQL Server多表关联后如何合并关联字段并获取唯一记录

问题根因 #

你现在拿到4条重复记录，本质是多对多关联产生的笛卡尔积：当1部电影对应2个演员、2个制片人时，直接关联4张表会生成2*2=4条结果，除了演员和制片人字段外其余字段完全重复。

推荐方案（SQL Server 2017及以上） #

使用官方提供的STRING_AGG聚合函数，先分别对每个电影的演员、制片人做字段拼接，再和电影主表关联，避免直接关联产生冗余数据：

SELECT 
    m.MovieId,
    m.MovieName,
    a.ActorName,
    p.ProducerName,
    m.ReleaseDate,
    m.Plot
FROM Movie m
-- 关联聚合后的演员列表
INNER JOIN (
    SELECT 
        am.MovieId,
        STRING_AGG(ac.ActorName, ',') AS ActorName
    FROM ActorMovie am
    INNER JOIN Actor ac ON am.ActorId = ac.ActorId
    GROUP BY am.MovieId
) a ON m.MovieId = a.MovieId
-- 关联聚合后的制片人列表
INNER JOIN (
    SELECT 
        pm.MovieId,
        STRING_AGG(pr.ProducerName, ',') AS ProducerName
    FROM ProducerMovie pm
    INNER JOIN Producer pr ON pm.ProducerId = pr.ProducerId
    GROUP BY pm.MovieId
) p ON m.MovieId = p.MovieId
-- 不需要筛选单部电影可删除下方WHERE条件
WHERE m.MovieId = 1

低版本兼容方案（SQL Server 2016及更早） #

如果你的数据库版本不支持STRING_AGG函数，可以使用FOR XML PATH语法实现同等拼接效果：

SELECT 
    m.MovieId,
    m.MovieName,
    STUFF((
        SELECT ',' + ac.ActorName
        FROM ActorMovie am
        INNER JOIN Actor ac ON am.ActorId = ac.ActorId
        WHERE am.MovieId = m.MovieId
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS ActorName,
    STUFF((
        SELECT ',' + pr.ProducerName
        FROM ProducerMovie pm
        INNER JOIN Producer pr ON pm.ProducerId = pr.ProducerId
        WHERE pm.MovieId = m.MovieId
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS ProducerName,
    m.ReleaseDate,
    m.Plot
FROM Movie m
-- 不需要筛选单部电影可删除下方WHERE条件
WHERE m.MovieId = 1
GROUP BY m.MovieId, m.MovieName, m.ReleaseDate, m.Plot

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