搜索列并排除代码

Question

我正在尝试提取与"Smith"和日期范围匹配的记录列表，并排除任何代码为 DD2530 和 PGC 的帐号。 当我运行它时，我得到了史密斯和日期范围，但这些代码没有从查询中排除。

select *
from [A05_AccountCommunications] a05
join (
    select distinct (a05.AccountNumber)
    from [A05_AccountCommunications] a05
    join [A01cAccountCodes] a01c on a05.accountnumber = a01c.AccountNumber
        and (a01c.codevalue not in ('dd2530'))
    join [A01cAccountCodes] a01c2 on a01c.accountnumber = a01c2.AccountNumber
        and (a01c2.codevalue not in ('PGC'))
    ) tm on a05.AccountNumber = tm.AccountNumber
where a05.date >= DATEADD(MONTH, - 24, GETDATE())
    and (
        a05.LongComment like '%Smith%'
        or a05.ShortComment like '%Smith%'
        )
order by a05.AccountNumber,
    a05.date desc

Answer 1

试试这个：

已编辑以排除重复项。

select a05.*
from [A05_AccountCommunications] a05
     join (select distinct a01c.AccountNumber 
           from  [A01cAccountCodes] a01c
           where a01c.codevalue not in ('dd2530', 'PGC')
          ) ao1c  on a05.accountnumber = a01c.AccountNumber
where a05.date >= DATEADD(MONTH, - 24, GETDATE())
      and (
            a05.LongComment like '%Smith%'
            or a05.ShortComment like '%Smith%'
          )
order by a05.AccountNumber, a05.date desc

Answer 2

您需要排除 WHERE 子句中的代码，而不是 ON 语句。见下文：

select *
from [A05_AccountCommunications] a05
join [A01cAccountCodes] a01c on a05.accountnumber = a01c.AccountNumber
where a05.date >= DATEADD(MONTH, - 24, GETDATE())
    and (
      a05.LongComment like '%Smith%'
      or a05.ShortComment like '%Smith%'
      )
    and a01c.codevalue not in ('dd2530', 'PGC')
order by a05.AccountNumber,
    a05.date desc