加入多个表返回NULL值

2021-12-10 00:00:00 join tsql sql-server sql-server-2012

我有 3 种不同的 CTE 结果，我需要相互加入:

I have 3 different outcomes of CTE that I need to LEFT JOIN each other:

主表 @Policies 包含所有 VehiclePolicyLimitsID 值:

Main Table @Policies contains all VehiclePolicyLimitsID values:

@LiabilityPremium:

@HiredPremium:

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

As an example I mimic the results of CTE's into 3 table variables:

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

但出于某种原因，LiabilityPremium 的结果全部为 NULL:

But for some reason the outcome of LiabilityPremium is all NULL's:

我希望结果看起来像这样，总的 LiabilityPremium = $3,736

I would expect the result looks like this with total LiabilityPremium = $3,736

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

Is any way to join somehow to receive desirable result?

推荐答案

那是因为加法运算符两边的 null 将产生 null 的结果.您可以使用 ISNULL(LiabilityPremium, 0) 示例:

That is because null on either side of the addition operator will yield a result of null. You can use ISNULL(LiabilityPremium, 0) Example:

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

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

or you can use COALESCE instead of ISNULL.

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

<小时>

编辑

我不确定这是与这个小数据集的巧合还是预期的，但是如果总是预期 @LiabilityPremium.LiabilityPremium 或 @HiredPremium.LiabilityPremium 将始终为 null 则无需执行加法.而是直接在这两列上使用 COALESCE.

Edit

I am not sure if this is coincidence with this small data set or expected but if it is always expected that either @LiabilityPremium.LiabilityPremium or @HiredPremium.LiabilityPremium will always be null then there is no need to perform addition. Instead use COALESCE directly on those 2 columns.

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

相关文章