两表关联且关联列值不唯一时CASE WHEN查询行数异常增多问题

问题根源 #

你遇到的行数增长是一对多关联导致的行复制问题：OrganizationInRole表中单个OrganizationId对应多条角色记录，直接LEFT JOIN后主表的每行都会匹配到多条oir表记录，最终生成重复行。

解决方案 #

方案1：用EXISTS子查询实现逻辑（推荐） #

完全去掉对oir表的JOIN，直接通过子查询判断是否存在符合条件的角色记录，不会产生额外关联行，逻辑更简洁：

Select  r.Id,
        r.RequiredOn AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' 
               as RequiredDate,
        Concat(vs.Salutation, ' ',vs.FirstName, ' ', vs.LastName) as Name,
        oo.Name as RequestingOrganization,
        o.Name as Location,
        Case
            When r.IntendedOutcome = '1' Then 'T'
            When r.IntendedOutcome = '2' Then 'R'
        End as RequestType,
        etr.TypeRequested,
        Case
            When etr.Identifier is not null then etr.Identifier
            When etr.Identifier is null then ' '
        End as Identifier,
        f.OfferedOn,
        f.OfferResponse,
        r.DestinationCountryCodes,
        o.Id,
        -- 替换原CASE逻辑，用EXISTS判断是否存在符合要求的角色记录
        CASE
            WHEN EXISTS (
                SELECT 1 FROM dbo.OrganizationInRole oir 
                WHERE oir.OrganizationId = o.Id 
                AND oir.OrganizationRoleId = 'de51c814-f86d-49c9-941b-999a98be4894'
            )
            THEN 1
            ELSE NULL
       END AS Bk1

From [Request] r
    Left Join Recovered etr
    on etr.DistributionRequestId = r.Id      

    Left Join [Offer] f
        on f.Id = etr.Id

        Left Join [dbo].Contact vs
            on vs.Id = r.SId

                Left Join [dbo].Organization o
                    on o.Id = r.SLocationId or o.Id = r.RLocationId

                        Left Join [dbo].Organization oo
                            on oo.Id = r.RequestingOrganizationId
-- 删掉原来的oir表JOIN逻辑
 Where f.Response = 'Accepted' or f.Response is NULL

方案2：预聚合oir表后再关联 #

如果后续需要用到oir表的其他字段，可以先对oir表做去重处理，保证每个OrganizationId只有一行后再关联，也不会出现行数膨胀：

Select  r.Id,
        r.RequiredOn AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' 
               as RequiredDate,
        Concat(vs.Salutation, ' ',vs.FirstName, ' ', vs.LastName) as Name,
        oo.Name as RequestingOrganization,
        o.Name as Location,
        Case
            When r.IntendedOutcome = '1' Then 'T'
            When r.IntendedOutcome = '2' Then 'R'
        End as RequestType,
        etr.TypeRequested,
        Case
            When etr.Identifier is not null then etr.Identifier
            When etr.Identifier is null then ' '
        End as Identifier,
        f.OfferedOn,
        f.OfferResponse,
        r.DestinationCountryCodes,
        o.Id,
        CASE
            WHEN oir.OrganizationId IS NOT NULL
            THEN 1
            ELSE NULL
       END AS Bk1

From [Request] r
    Left Join Recovered etr
    on etr.DistributionRequestId = r.Id      

    Left Join [Offer] f
        on f.Id = etr.Id

        Left Join [dbo].Contact vs
            on vs.Id = r.SId

                Left Join [dbo].Organization o
                    on o.Id = r.SLocationId or o.Id = r.RLocationId

                        Left Join [dbo].Organization oo
                            on oo.Id = r.RequestingOrganizationId
                            -- 替换原JOIN逻辑，预筛选去重oir表
                            Left Join (
                                SELECT DISTINCT OrganizationId 
                                FROM dbo.OrganizationInRole 
                                WHERE OrganizationRoleId = 'de51c814-f86d-49c9-941b-999a98be4894'
                            ) oir
                                on oir.OrganizationId = o.Id

 Where f.Response = 'Accepted' or f.Response is NULL

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