代码之家 › 专栏 › 技术社区 › Serdia

联接多个表返回空值

sql-server-2012 join tsql sql-server

Serdia · 技术社区 · 6 年前

我有三种不同的CTE结果,我需要彼此加入:

主表 @Policies 包含全部 VehiclePolicyLimitsID 价值观:

@LiabilityPremium :

@HiredPremium :

例如,我将CTE的结果模拟成3个表变量:

declare @Policies table (VehiclePolicyLimitsID int)
insert into @Policies values (2101891),
                             (2101892),
                             (2101893),
                             (2101894),
                             (2119235),
                             (2119236),
                             (2119237),
                             (2119238),
                             (2190860),
                             (2190861),
                             (2190862),
                             (2190863)
--select * from @Policies


declare @LiabilityPremium  table (Quoteid int, ClassCode int, VehiclePolicyLimitsID int, LiabilityPremium money)
insert into @LiabilityPremium values (728436,3199,2101892,1723),
                                     (728436, 23199,2101893,1855),
                                     (728436,68199,2101894,133),
                                     (741626,3199,2119236,0),
                                     (741626,23199,2119237,0),
                                     (741626,68199,2119238,0),
                                     (774168,3199,2190861,0),
                                     (774168,23199,2190862,0),
                                     (774168,68199,2190863,0)
--select * from @LiabilityPremium

declare @HiredPremium  table (Quoteid int, ClassCode int, VehiclePolicyLimitsID int, LiabilityPremium money)
                    insert into @HiredPremium values ( 728436,  NULL,   2101891,    25),
                                                     (741626,   NULL,   2119235,    0),
                                                     (774168,   NULL,   2190860,    0)

--select * from @HiredPremium
    select 
            COALESCE(l.Quoteid,h.QuoteID,'') as QuoteID,
            COALESCE(l.ClassCode,h.ClassCode,'') as ClassCode,
            COALESCE(l.VehiclePolicyLimitsID,h.VehiclePolicyLimitsID,'') as VehiclePolicyLimitsID,
            l.LiabilityPremium + h.LiabilityPremium as LiabilityPremium
    from @Policies p
    left join @LiabilityPremium l ON l.VehiclePolicyLimitsID = p.VehiclePolicyLimitsID
    left join @HiredPremium h ON h.VehiclePolicyLimitsID = p.VehiclePolicyLimitsID

但由于某些原因,负债性贫血的结果都是空的:

我希望结果是这样的,总负债率=3736美元

有没有什么方法可以加入进来以获得理想的结果?

3 回复 | 直到 6 年前

Igor 6 年前

那是因为 null 加法运算符的两边都将产生 无效的 . 你可以用 ISNULL(LiabilityPremium, 0) 例子:

ISNULL(l.LiabilityPremium,0) + ISNULL(h.LiabilityPremium,0) as LiabilityPremium

或者你可以用 COALESCE 而不是 ISNULL .

COALESCE(l.LiabilityPremium,0) + COALESCE(h.LiabilityPremium,0) as LiabilityPremium

编辑

我不确定这是否符合这个小数据集或预期,但如果人们总是希望 @LiabilityPremium.LiabilityPremium 或 @HiredPremium.LiabilityPremium 将始终为空,则无需执行添加。而是使用 聚结 直接在那两列上。

COALESCE(l.LiabilityPremium, h.LiabilityPremium) as LiabilityPremium

alwaysVBNET 6 年前

COALESCE(l.LiabilityPremium,0) + COALESCE(h.LiabilityPremium,0) as LiabilityPremium

Marcus Vinicius Pompeu 6 年前

那是因为

l.LiabilityPremium + h.LiabilityPremium

如果两者中的任何一个为空,则表达式为空。

这个表达式应该可以修复它

COALESCE(l.LiabilityPremium, 0.00) + COALESCE(h.LiabilityPremium, 0.00)