SQL：连接条件下的ISNULL

本文介绍了SQL：连接条件下的ISNULL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

$ 有没有其他方法可以写这个。如果Application表中的Credit cardID为NULL，则从Terminatedcreditcard表加入CreditcardID。谢谢，

Hi, Is there any other way to write this. If the Credit cardIDin Application table is NULL, then join CreditcardIDfrom Terminatedcreditcard table. Thanks,

选择Distinct prg.Title as Program ，a.Patientid ，a.Applicationid ，PT.MCC ，PT.MerchantName ，PT.MerchantCity ，PT.MerchantState ，PT.MerchantZip ，PT.SettlementTransactionID ，将（日期，PT.SettlementDate）转换为SettlementDate ，ABS（PT.Amount）为TransactionAmount来自[HydraDB]的 。[dbo]。[StagingLLSPEXSett lements] PT $ 左加入HYDRADB..Application a PT上的 。[CustomId] = ISnull（a.CreditCardId，TC.CreditCardID） 左加入HYDRADB..TerminatedCreditCard TC on TC.ApplicationId = a.ApplicationId

select Distinct prg.Title as Program , a.Patientid , a.Applicationid , PT.MCC , PT.MerchantName , PT.MerchantCity , PT.MerchantState , PT.MerchantZip , PT.SettlementTransactionID , convert(Date,PT.SettlementDate) as SettlementDate , ABS(PT.Amount) as TransactionAmount from [HydraDB].[dbo].[StagingLLSPEXSettlements] PT Left Join HYDRADB..Application a on PT.[CustomId] = ISnull(a.CreditCardId, TC.CreditCardID) Left join HYDRADB..TerminatedCreditCard TC on TC.ApplicationId = a.ApplicationId

推荐答案

选择Distinct prg.Title as程序 ，x.Patientid ，x.Applicationid ，PT.MCC ，PT.MerchantName ，PT.MerchantCity ，PT。 MerchantState ，PT.MerchantZip ，PT.SettlementTransactionID ，转换（日期，PT.SettlementDate）作为SettlementDate ，ABS（PT.Amount）为TransactionAmount <来自[HydraDB]。[dbo]。[StagingLLSPEXSettlements] PT 左连接（选择一个.Patientid ，a.Applicationid ，ISnull（a.CreditCardId，TC.CreditCardID）为CreditCardId 来自HYDRADB..Application a 左连接HYDRADB..TerminatedCreditCard TC on TC.ApplicationId = a.ApplicationId   ; ）x PT。[CustomId] = x.CreditCardId select Distinct prg.Title as Program , x.Patientid , x.Applicationid , PT.MCC , PT.MerchantName , PT.MerchantCity , PT.MerchantState , PT.MerchantZip , PT.SettlementTransactionID , convert(Date,PT.SettlementDate) as SettlementDate , ABS(PT.Amount) as TransactionAmount from [HydraDB].[dbo].[StagingLLSPEXSettlements] PT Left Join (select a.Patientid ,a.Applicationid ,ISnull(a.CreditCardId, TC.CreditCardID) as CreditCardId From HYDRADB..Application a Left join HYDRADB..TerminatedCreditCard TC on TC.ApplicationId = a.ApplicationId )x on PT.[CustomId] = x.CreditCardId