sql - 删除重复的左外连接

Question

我需要帮助来合并来 self 的 sql 查询的以下结果的 2 个重复行。

结果

查询

SELECT DISTINCT
    a.[LocationID],
    a.[BuildingCode],
    a.[LocationCode],
    a.[LocationName],
    c.UserName,
    d.RoleName  
FROM 
    [dbo].[Location] a
LEFT OUTER JOIN 
    [dbo].[UserLocation] b ON a.LocationID = b.LocationID
LEFT OUTER JOIN 
    [dbo].[User] c ON b.UserID = c.UserID AND c.RoleID = 2
LEFT OUTER JOIN 
    [dbo].[Role] d ON c.RoleID = d.RoleID

如果您可以看到有 2 行的 LocationId 为 3057，一行为 Role Admin，另一行为 NULL。我想合并它们并希望只显示一行 Role Admin。

感谢您关注此问题。

Answer 1

试试这个:

SELECT
    a.[LocationID],
    a.[BuildingCode],
    a.[LocationCode],
    a.[LocationName],
    MAX(c.UserName) UserName,
    MAX(d.RoleName ) RoleName 
FROM 
    [dbo].[Location] a
LEFT OUTER JOIN 
    [dbo].[UserLocation] b ON a.LocationID = b.LocationID
LEFT OUTER JOIN 
    [dbo].[User] c ON b.UserID = c.UserID AND c.RoleID = 2
LEFT OUTER JOIN 
    [dbo].[Role] d ON c.RoleID = d.RoleID
GROUP BY 
    a.[LocationID],
    a.[BuildingCode],
    a.[LocationCode],
    a.[LocationName]