我有3种不同的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
但是由于某种原因,LiabilityPremium的结果全为NULL:
我希望结果看起来像这样,总负债溢价= $ 3,736
有什么办法可以通过某种方式获得理想的结果吗?
那是因为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
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句