gpt4 book ai didi

sql-server - 连接多个表返回 NULL 值

转载 作者:行者123 更新时间:2023-12-02 22:58:20 24 4
gpt4 key购买 nike

我有 3 种不同的 CTE 结果,我需要将它们相互左连接:

主表@Policies包含所有VehiclePolicyLimitsID值:

enter image description here

@LiabilityPremium:

enter image description here

@HiredPremium:

enter image description here

作为示例,我将 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

但由于某种原因,LiabilityPremium 的结果全部为 NULL:

enter image description here

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

enter image description here

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

最佳答案

这是因为加法运算符两侧的 null 将产生 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 将始终为 null,则无需执行加法。而是直接在这两列上使用 COALESCE

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

关于sql-server - 连接多个表返回 NULL 值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51713528/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com