SQL Server中如何关联两组已分别完成LEFT JOIN的表查询结果

你现有的两组查询共享MoviePersonRole作为关联桥梁，无需分开查询后再拼接，直接一次关联所有表即可拿到全量结果，也可以通过CTE封装两组查询后再关联，两种实现方式如下：

方案1：直接关联所有表（性能最优，推荐使用） #

SELECT 
    mp.PersonId, 
    mp.PersonFirstName, 
    mp.PersonLastName, 
    mpr.RoleId,
    d.DVDId, 
    d.GenreId, 
    d.RatingId, 
    d.DVDTitle, 
    d.DVDReleasedate, 
    d.TheaterReleaseDate, 
    g.GenreName, 
    r.RatingName,
    r.RatingDescription
FROM MoviePerson mp
LEFT JOIN MoviePersonRole mpr ON mp.PersonId = mpr.PersonId
LEFT JOIN DVD d ON mpr.DVDId = d.DVDId
LEFT JOIN Genre g ON d.GenreId = g.GenreID
LEFT JOIN Rating r ON d.RatingId = r.RatingId;

说明：通过表别名简化了写法，按依赖关系依次拼接所有关联表，一次查询即可拿到人员、角色、DVD、分类、分级的全量关联数据。

方案2：封装子查询后关联（匹配你原有拆分查询的逻辑） #

你之前子查询逻辑混乱的核心原因是第一组查询未带出用于关联的DVDId字段，补充该字段后两个结果集即可通过共有的DVDId完成关联：

WITH person_role AS (
    -- 第一组查询：人员与角色关联结果，补充DVDId作为关联字段
    SELECT 
        MoviePerson.PersonId, 
        MoviePerson.PersonFirstName, 
        MoviePerson.PersonLastName, 
        MoviePersonRole.RoleId,
        MoviePersonRole.DVDId
    FROM MoviePerson
    LEFT JOIN MoviePersonRole ON MoviePerson.PersonId = MoviePersonRole.PersonId
),
dvd_info AS (
    -- 第二组查询：DVD相关关联结果
    SELECT 
        DVD.DVDId, 
        DVD.GenreId, 
        DVD.RatingId, 
        DVD.DVDTitle, 
        DVD.DVDReleasedate, 
        DVD.TheaterReleaseDate, 
        Genre.GenreName, 
        Rating.RatingName,
        Rating.RatingDescription
    FROM DVD
    LEFT JOIN Genre ON DVD.GenreId = Genre.GenreID
    LEFT JOIN Rating ON DVD.RatingId = Rating.RatingId
)
-- 两个结果集通过DVDId关联
SELECT * FROM person_role pr
LEFT JOIN dvd_info di ON pr.DVDId = di.DVDId;

调整说明 #

• 若需保留所有DVD数据（即使没有对应关联的人员），可将最后的LEFT JOIN替换为RIGHT JOIN，或者调换两个结果集的关联顺序
• 若需要结果去重，可在SELECT后添加DISTINCT，也可根据业务需求补充WHERE条件过滤数据

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