以下查询的Not In子句的等效连接子句

Question

我有以下问题

select 
    'Un-contacted Cases'as Header, COUNT(a.Id) as Nos,
    ISNULL(SUM(convert(decimal(18, 2), (isnull(PRINCIPAL_OD, 0)))), 0) as Amt
from 
    LoanAccounts a 
where
    a.Id not in (select b.AccountId  
                 from Feedback b 
                 where OutcomeCurrentAction in ('ptp','Contact new PTP','Contact- No new PTP','Contact- No PTP','Met-No-PTP','New PTP','Payment/Direct Deposit') 
                )

这将返回以下输出：

Header              Nos      Amt
----------------------------------------
Un-contacted Cases  1149    131942525.00

但是，在尝试使用left join编写相同的查询时，我得到了不同的输出

select  
    'Un-contacted Cases' as Header, COUNT(a.Id) as Nos,
    ISNULL(SUM(convert(decimal(18, 2), (isnull(PRINCIPAL_OD, 0)))), 0) as Amt
from 
    LoanAccounts a 
left join 
    Feedback b on b.AccountId = a.Id 
where 
    OutcomeCurrentAction in ('ptp','Contact new PTP','Contact- No new PTP','Contact- No PTP','Met-No-PTP','New PTP','Payment/Direct Deposit') 

这将返回以下输出：

Header              Nos      Amt
---------------------------------------
Un-contacted Cases  51      6026486.00

对于上述不在-中的连接，什么可以是等价的连接？

Answer 1

它看起来是这样的：

select 'Un-contacted Cases'as Header, COUNT(a.Id) as Nos,
       COALESCE(SUM(convert(decimal(18, 2), PRINCIPAL_OD)), 0) as Amt
from LoanAccounts a left join
     Feedback b
     on b.AccountId = a.Id and 
        b.OutcomeCurrentAction 
     in ('ptp','Contact new PTP','Contact- No new PTP','Contact- No PTP','Met-No-PTP','New PTP','Payment/Direct Deposit') 
where b.AccountId is null;

我还修好了你的SUM()。你的版本比必要的复杂。实际上我觉得你真的想：

       convert(decimal(18, 2) coalesce(sum(PRINCIPAL_OD), 0) as Amt

在对小数进行算术时，最好在算术之后进行转换--如果这是您真正想要的数据类型。

Answer 2

你可以像下面这样尝试

select 'Un-contacted Cases'as Header, COUNT(a.Id) as Nos,ISNULL(SUM(convert(decimal(18,2),(isnull(PRINCIPAL_OD,0)))),0) as Amt
from LoanAccounts a
left join
(
select b.AccountId  from Feedback b 
    where   OutcomeCurrentAction in ('ptp','Contact new PTP','Contact- No new PTP','Contact- No PTP','Met-No-PTP','New PTP','Payment/Direct Deposit') 
) a1 on a.Id =a1.AccountId  
  where a1.AccountId  is null

Answer 3

如果您正在对表b执行左连接，则不能将表b中的行上的过滤器放入where子句中(除非它要查找空值)。where子句实际上将左连接转换为内连接，因为表b中不存在的行将始终具有空字段值。

因此，您需要将where子句内容放入左连接条件中。如下所示：

select  
'Un-contacted Cases' as Header, COUNT(a.Id) as Nos,
ISNULL(SUM(convert(decimal(18, 2), (isnull(PRINCIPAL_OD, 0)))), 0) as Amt
from 
LoanAccounts a 
left join 
Feedback b on b.AccountId = a.Id 
and
b.OutcomeCurrentAction in ('ptp','Contact new PTP','Contact- No new PTP','Contact- No PTP','Met-No-PTP','New PTP','Payment/Direct Deposit')
where b.AccountId is null

这基本上与戈登的答案相同，但只是为了解释原因。