连接多个表将返回NULL值 [英] Joining multiple tables returns NULL value
问题描述
我有3种不同的CTE结局,我需要相互保持联系:
I have 3 different outcomes of CTE that I need to LEFT JOIN each other:
主表@Policies
包含所有VehiclePolicyLimitsID
值:
@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:
我希望结果看起来像这样,总Lipremium = $ 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,则无需执行加法.而是直接在这2列上使用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
这篇关于连接多个表将返回NULL值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!