使用聯合的SQL連接

無法理解聯合查詢如果在沒有連接的情況下運行它就可以正常作業的問題是什么，但我需要在左連接中使用它。當我嘗試時，它向我顯示了該列 DisplayName 多次使用的錯誤。我做錯了什么？

    LEFT JOIN (
    SELECT
        p.Id,
        zvl.DisplayName,
        dlz.DisplayName 
    FROM
        cm.Projects p
        LEFT JOIN cm.ProjectVisualBlocks pvb ON pvb.ProjectId= p.Id
        JOIN DBL.[Bl_2b987e3e-4939-e711-80c8-000d3af7ae73] b ON pvb.Id = b.ProjectVisualBlockId
        LEFT JOIN cm.Participants [User] ON [User].Id= b.[4a789414-4a39-e711-80c8-000d3af7ae73]
        LEFT JOIN cm.Participants [User_A] ON [User_A].Id= b.[4c789414-4a39-e711-80c8-000d3af7ae73] 
    WHERE
        p.ProjectTypeId= '526ba5f2-4c39-e711-80c8-000d3af7ae73' 
UNION
    SELECT
        p.Id,
        zvl.DisplayName,
        dlz.DisplayName 
    FROM
        cm.Projects p
        LEFT JOIN (
        SELECT
            pp.Id,
            d.DisplayName 
        FROM
            cm.Projects pp
            LEFT JOIN cm.ProjectVisualBlocks pvb ON pvb.ProjectId= pp.Id
            JOIN cm.ProjectLines pl ON pvb.Id = pl.ProjectVisualBlockId
            JOIN DBL.[Ln_F1CC2727-45C5-E711-80C2-0CC47A966E25] ln ON ln.ProjectLineId = pl.Id 
            AND ln.ProjectVisualBlockId = pvb.Id
            JOIN cm.Participants d ON d.Id= ln.[F2CC2727-45C5-E711-80C2-0CC47A966E25] 
        ) dlz ON dlz.Id= p.Id
        LEFT JOIN (
        SELECT
            p.Id,
            [User].DisplayName 
        FROM
            cm.Projects p
            LEFT JOIN cm.ProjectVisualBlocks pvb ON pvb.ProjectId= p.Id
            JOIN cm.ProjectLines pl ON pvb.Id = pl.ProjectVisualBlockId
            JOIN DBL.[Ln_A25439AA-B51B-E811-80C2-0CC47A966E25] l ON l.ProjectLineId = pl.Id 
            AND l.ProjectVisualBlockId = pvb.Id
            JOIN cm.Participants [User] ON [User].Id= l.[F0CC2727-45C5-E711-80C2-0CC47A966E25] 
        ) zvl ON zvl.Id= p.Id 
    WHERE
    p.ProjectTypeId= 'b498aad9-3ec5-e711-80c2-0cc47a966e25' 
    ) xavdol ON xavdol.Id= p.Id

uj5u.com熱心網友回復：

您已經使用了兩次 DisplayName 列，請更改為：

LEFT JOIN (
    SELECT
        p.Id,
        zvl.DisplayName,
        dlz.DisplayName as DisplayName2
    FROM
        cm.Projects p
        LEFT JOIN cm.ProjectVisualBlocks pvb ON pvb.ProjectId= p.Id
        JOIN DBL.[Bl_2b987e3e-4939-e711-80c8-000d3af7ae73] b ON pvb.Id = b.ProjectVisualBlockId
        LEFT JOIN cm.Participants [User] ON [User].Id= b.[4a789414-4a39-e711-80c8-000d3af7ae73]
        LEFT JOIN cm.Participants [User_A] ON [User_A].Id= b.[4c789414-4a39-e711-80c8-000d3af7ae73] 
    WHERE
        p.ProjectTypeId= '526ba5f2-4c39-e711-80c8-000d3af7ae73' 
UNION
    SELECT
        p.Id,
        zvl.DisplayName,
        dlz.DisplayName  as DisplayName2
    FROM
        cm.Projects p
        LEFT JOIN (
        SELECT
            pp.Id,
            d.DisplayName 
        FROM
            cm.Projects pp
            LEFT JOIN cm.ProjectVisualBlocks pvb ON pvb.ProjectId= pp.Id
            JOIN cm.ProjectLines pl ON pvb.Id = pl.ProjectVisualBlockId
            JOIN DBL.[Ln_F1CC2727-45C5-E711-80C2-0CC47A966E25] ln ON ln.ProjectLineId = pl.Id 
            AND ln.ProjectVisualBlockId = pvb.Id
            JOIN cm.Participants d ON d.Id= ln.[F2CC2727-45C5-E711-80C2-0CC47A966E25] 
        ) dlz ON dlz.Id= p.Id
        LEFT JOIN (
        SELECT
            p.Id,
            [User].DisplayName 
        FROM
            cm.Projects p
            LEFT JOIN cm.ProjectVisualBlocks pvb ON pvb.ProjectId= p.Id
            JOIN cm.ProjectLines pl ON pvb.Id = pl.ProjectVisualBlockId
            JOIN DBL.[Ln_A25439AA-B51B-E811-80C2-0CC47A966E25] l ON l.ProjectLineId = pl.Id 
            AND l.ProjectVisualBlockId = pvb.Id
            JOIN cm.Participants [User] ON [User].Id= l.[F0CC2727-45C5-E711-80C2-0CC47A966E25] 
        ) zvl ON zvl.Id= p.Id 
    WHERE
    p.ProjectTypeId= 'b498aad9-3ec5-e711-80c2-0cc47a966e25' 
    ) xavdol ON xavdol.Id= p.Id
sql
sql-server

標籤：sql sql服务器

上一篇：在一個表中查找其他表中不存在的行的最快方法

下一篇：使用遷移助手工具遷移多個資料庫-SQLSERVER到AZURESQL資料庫