本文介绍了左外连接的条件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 嘿伙计们,我一整天都没有这么做,我所尝试的一切都没有效果。我需要将条件放在where子句中作为我的连接的条件,即基于这些条件的左外连接dbo.ExchangeRate可以有人请帮助:) 来自 CTE cdr 内部 加入 dbo.Carrier c on c.CarrierId = cdr.CarrierId 内部 加入 dbo.RouteRanges rr 使用( NoLock ) On cdr.RouteRangeId = rr.RouteRangeId 内部 加入 dbo.Route r 使用( NoLock ) On rr.RouteId = r .RouteId 内部 加入 dbo.Switch s 使用( NoLock )开启 r.SwitchId = s .SwitchId 内部 加入 dbo.Destination d 使用( NoLock ) On cdr.DestinationId = d.DestinationId 内部 加入 dbo.DestinationBand db db.DestinationBandId = d.DestinationBandId 内部 加入 dbo.RateBatch b on b.BatchId = cdr.BatchId 内部 加入 dbo。 上的RateDeployBatch rdb b.DeployBatchid = rdb.DeployBatchId 左 外 加入 dbo.ExportRoamingDetails e on e.FileDate = rdb.RoamingDate 和 e.CarrierId = cdr.CarrierId 和 e.RAP = 0 左 外 加入 dbo.ExportRoamingExchangeRate e2 On rdb.RoamingDate = e2.FileDate Left Outer 加入 dbo.ExchangeRate er on e2 .ExchangeRateId = er.ExchangeRateId 左 外 加入 dbo.ExportFile ef on ef.ExportFileId = e.ExportFileId Inner 加入 dbo.Prefix p 使用( NoLock )开启 cdr.PrefixId = p.PrefixId 内部 加入 dbo.AccessTypeCode atc 使用( NoLock ) 开启 cdr.AccessTypeCodeId = atc.AccessTypeCodeId 内部 加入 dbo.CallClassCode ccc 使用( NoLock )开启 cdr.CallClassCodeId = ccc.CallClassCodeId 内部 加入 dbo。 PartialCallCode pcc 使用( NoLock )开启 cdr .PartialCallCodeId = pc c.PartialCallCodeId 内部 加入 dbo.QualityOfServiceCode qofc 使用( NoLock ) On cdr.QualityOfServiceCodeId = qofc.QualityOfServiceCodeId 内部 加入 dbo.TrafficTypeCode ttc 使用( NoLock ) On cdr.TrafficTypeCodeId = ttc.TrafficTypeCodeId Inner 加入 dbo.CDRFile f 使用( NoLock ) On cdr.FileId = f.FileId 内部 加入 #tempba ba cdr.CarrierId = ba.CarrierId 和 cdr.DestinationId = ba.DestinationId 其中​​ er.TargetCurrencyId = @CurrencyId er.CarrierId 空 和 er.Deleted = 0 干杯伙伴解决方案 你应该没问题,只需将它添加到左外连接的条件: 来自 CTE cdr 内部 加入 dbo.Carrier c on c.CarrierId = cdr.CarrierId 内部 加入 dbo.RouteRanges rr 使用( NoLock )开启 cdr.RouteRangeId = rr.RouteRangeId 内部 加入 dbo.Route r 使用( NoLock )开 rr.RouteId = r.RouteId 内部 加入 dbo.Switch s 使用( NoLock )开启 r.SwitchId = s.SwitchId 内部 加入 dbo.Destination d 使用( NoLock ) On cdr.DestinationId = d.DestinationId 内部 加入 dbo.DestinationBand db db。 DestinationBandId = d.DestinationBandId 内部 加入 dbo.RateBatch b on b。 BatchId = cdr.BatchId 内部 加入 dbo.RateDeployBatch rdb on b.DeployBatchid = rdb.DeployBatchId 左 外 加入 dbo.ExportRoamingDetails e on e.FileDate = rdb.RoamingDate 并且 e.CarrierId = cdr.CarrierId 和 e.RAP = 0 左 外 加入 dbo.ExportRoamingExchangeRate e2 On rdb.RoamingDate = e2.FileDate Left Outer 加入 dbo.ExchangeRate er on e2.ExchangeRateId = er.ExchangeRateId 和 er.TargetCurrencyId = @ CurrencyId 和 er.CarrierId Null 和 er.Deleted = 0 左 Outer 加入 dbo.ExportFile ef on ef.ExportFileId = e.ExportFileId 内部 加入 dbo.Prefix p 使用( NoLock ) On cdr.PrefixId = p。 PrefixId 内部 加入 dbo.AccessTypeCode atc 使用( NoLock )开启 cdr.AccessTypeCodeId = atc.AccessTypeCodeId 内部 加入 dbo.CallClassCode ccc 使用( NoLock )开启 cdr.CallClassCodeId = ccc.CallClassCodeId 内部 加入 dbo.PartialCallCode pcc 使用( NoLock )开启 cdr.PartialCallCodeId = pcc.PartialCallCodeId 内部 加入 dbo.QualityOfServiceCode qofc 使用( NoLock )开启 cdr.QualityOfServiceCodeId = qofc.QualityOfServiceCodeId 内部 加入 dbo .TrafficTypeCode ttc 使用( NoLock )开启 cdr.TrafficTypeCodeId = ttc.TrafficTypeCodeId 内部 加入 dbo.CDRFile f 使用( NoLock ) On cdr.FileId = f.FileId 内部 加入 #tempba ba on cdr.CarrierId = ba.CarrierId 和 cdr.DestinationId = ba.DestinationId Hey guys, im stumpted with this all day and nothing I have tried has worked. I need to place the condition in the where clause as a condition for my join i.e left outer join dbo.ExchangeRate based on these conditions can someone please help:) From CTE cdr Inner Joindbo.Carrier c on c.CarrierId = cdr.CarrierId Inner Joindbo.RouteRanges rr With(NoLock) On cdr.RouteRangeId = rr.RouteRangeId Inner Joindbo.Route r With(NoLock) On rr.RouteId = r.RouteId Inner Joindbo.Switch s With(NoLock) On r.SwitchId = s.SwitchId Inner Joindbo.Destination d With(NoLock) On cdr.DestinationId = d.DestinationId Inner Join dbo.DestinationBand db on db.DestinationBandId = d.DestinationBandId Inner Join dbo.RateBatch b on b.BatchId = cdr.BatchId Inner Join dbo.RateDeployBatch rdb on b.DeployBatchid = rdb.DeployBatchId Left Outer Join dbo.ExportRoamingDetails e on e.FileDate = rdb.RoamingDate And e.CarrierId = cdr.CarrierId And e.RAP = 0 Left Outer Join dbo.ExportRoamingExchangeRate e2 On rdb.RoamingDate = e2.FileDate Left Outer Join dbo.ExchangeRate er on e2.ExchangeRateId = er.ExchangeRateId Left Outer Join dbo.ExportFile ef on ef.ExportFileId = e.ExportFileId Inner Joindbo.Prefix p With(NoLock) On cdr.PrefixId = p.PrefixId Inner Joindbo.AccessTypeCode atc With(NoLock) On cdr.AccessTypeCodeId = atc.AccessTypeCodeId Inner Joindbo.CallClassCode ccc With(NoLock) On cdr.CallClassCodeId = ccc.CallClassCodeId Inner Joindbo.PartialCallCode pcc With(NoLock) On cdr.PartialCallCodeId = pcc.PartialCallCodeId Inner Joindbo.QualityOfServiceCode qofc With(NoLock) On cdr.QualityOfServiceCodeId = qofc.QualityOfServiceCodeId Inner Joindbo.TrafficTypeCode ttc With(NoLock) On cdr.TrafficTypeCodeId = ttc.TrafficTypeCodeId Inner Joindbo.CDRFile f With(NoLock) On cdr.FileId = f.FileId Inner Join#tempba ba on cdr.CarrierId = ba.CarrierId and cdr.DestinationId = ba.DestinationIdWhereer.TargetCurrencyId = @CurrencyIdAnder.CarrierId Is Null and er.Deleted = 0Cheers Guys 解决方案 You should be fine just adding it to the conditions on your Left Outer Join:From CTE cdr Inner Joindbo.Carrier c on c.CarrierId = cdr.CarrierId Inner Joindbo.RouteRanges rr With(NoLock) On cdr.RouteRangeId = rr.RouteRangeId Inner Joindbo.Route r With(NoLock) On rr.RouteId = r.RouteId Inner Joindbo.Switch s With(NoLock) On r.SwitchId = s.SwitchId Inner Joindbo.Destination d With(NoLock) On cdr.DestinationId = d.DestinationId Inner Joindbo.DestinationBand db on db.DestinationBandId = d.DestinationBandId Inner Joindbo.RateBatch b on b.BatchId = cdr.BatchId Inner Joindbo.RateDeployBatch rdb on b.DeployBatchid = rdb.DeployBatchId Left Outer Joindbo.ExportRoamingDetails e on e.FileDate = rdb.RoamingDate And e.CarrierId = cdr.CarrierId And e.RAP = 0 Left Outer Joindbo.ExportRoamingExchangeRate e2 On rdb.RoamingDate = e2.FileDate Left Outer Joindbo.ExchangeRate er on e2.ExchangeRateId = er.ExchangeRateId and er.TargetCurrencyId = @CurrencyId and er.CarrierId Is Null and er.Deleted = 0Left Outer Joindbo.ExportFile ef on ef.ExportFileId = e.ExportFileId Inner Joindbo.Prefix p With(NoLock) On cdr.PrefixId = p.PrefixId Inner Joindbo.AccessTypeCode atc With(NoLock) On cdr.AccessTypeCodeId = atc.AccessTypeCodeId Inner Joindbo.CallClassCode ccc With(NoLock) On cdr.CallClassCodeId = ccc.CallClassCodeId Inner Joindbo.PartialCallCode pcc With(NoLock) On cdr.PartialCallCodeId = pcc.PartialCallCodeId Inner Joindbo.QualityOfServiceCode qofc With(NoLock) On cdr.QualityOfServiceCodeId = qofc.QualityOfServiceCodeId Inner Joindbo.TrafficTypeCode ttc With(NoLock) On cdr.TrafficTypeCodeId = ttc.TrafficTypeCodeId Inner Joindbo.CDRFile f With(NoLock) On cdr.FileId = f.FileId Inner Join#tempba ba on cdr.CarrierId = ba.CarrierId and cdr.DestinationId = ba.DestinationId 这篇关于左外连接的条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
08-04 16:14