包含空值的SQL内连接

Question

我目前正在扩展我几个月前编写的一个客户列表查询，以包含更多关于上一次定期审查的信息。工作是在Teradata上运行我们的数据仓库。下面是我正在使用的代码片段，实际的查询大约是200行。

SELECT DISTINCT
k.customerID
,k.name
,a.CountryCode
,CASE WHEN Account.actorID IS NOT NULL THEN 1 ELSE 0 END AS hasAccount
,id.ControlDate
,id.ControlBy
FROM customer k
LEFT JOIN agreement a ON k.actorID = a.actorID
LEFT JOIN identification id ON k.actorID = id.actorID
INNER JOIN (SELECT DISTINCT actorID, MAX(ControlDate) AS LastControl FROM identification GROUP BY actorID) id2 
   ON k.actorID = id2.actorID AND id.ControlDate = id2.LastControl
LEFT JOIN (SELECT DISTINCT actorID FROM agreement a WHERE a.activeAgreement = 'Y' and a.Product IN ('6774', '6775') Account ON k.actorid = Account.actorID
WHERE
k.customerstatus = 'Active'
;

问题在于INNER JOIN语句。当我运行这个程序时，我会得到1769行，但是如果我删除了INNER JOIN和两个id。在SELECT中的短语，这个总和会弹出到2117。区别是NULL值在id.ControlDate上。

但是，如果我使用LEFT JOIN而不是INNER JOIN，我会得到大约6800行，因为许多客户已经多次被更新/执行了控制。我怎么才能解决这个问题？

编辑：，为了澄清，我希望每个actorID有一行，在这里我可以得到最新的controlDate或NULL值。

Edit2:根据@Thorsten Kettner的请求，，一种解释。ActorID和CustomerID对于每个客户来说都是独一无二的。然而，这个值是由系统产生的，而customerID通常是一个社会保险号码、公司注册号等。我们倾向于在我们的客户关系管理系统中使用CustomerID作为查找值。一个客户(或参与者)可以根据他们与我们的关系达成许多协议，以及许多定期审查，因为法律要求我们定期进行投资组合审核。以下是一些样本数据：

( 1)不使用INNER JOIN语句：

actorID Customer_name Country hasAccount ControlDate ControlBy 278美国银行228美国国家银行贸易业务0？？ 275.330分行银行及信托公司美国0 04.02.2016 AD09853 275.2018年12月03.2018 AB96358美国国民银行 275169美国国民银行1 16.11.2016 AB02890 275.169美国国民银行1 15.12.2015 AB62775 275169美国国民银行1111.10.2011 AB68786 264摩根大通证券公司。贷款资产管理0 11.10.2017 AB45546 国际开发协会美国0 29.05.2018 AB45546 263个准995个Zions N.A美国1 19.03.2015 AB43584 263个Zions N.A美国109.11.2016 AB02890 263家Zions N.A美国1 13.03.2018 AB45546 263个Zions N.A美国106.10.2011 AB68786 263家花旗全球市场公司(花旗全球市场公司)1 22.12.2015 AB62775 263个花旗全球市场公司2012年12月04.2012 AB68786 262个Prebon金融产品公司美国0 30.12.2015 AB24733 262 H股113 JP摩根证券有限公司美国0 18.06.2018 AB45546 261美国联邦储备系统2015年11月1日AB62759 261.795联邦储备系统0 05.06.2014 AB31660

2)使用INNER JOIN语句：

actorID Customer_name Country hasAccount ControlDate ControlBy 275.330分行银行及信托公司美国0 04.02.2016 AD09853 275.2018年12月03.2018 AB96358美国国民银行 264摩根大通证券公司。贷款资产管理0 11.10.2017 AB45546 国际开发协会美国0 29.05.2018 AB45546 263家Zions N.A美国1 13.03.2018 AB45546 263家花旗全球市场公司(花旗全球市场公司)1 22.12.2015 AB62775 262个Prebon金融产品公司美国0 30.12.2015 AB24733 262 H股113 JP摩根证券有限公司美国0 18.06.2018 AB45546 261美国联邦储备系统2015年11月1日AB62759

正如你所看到的，actorID 278 228消失了，这是不好的.

Answer 1

您可以在TOP 1 WITH TIES排序上使用ROW_NUMBER来获取记录，其中只包含每个客户的最新日期。

select 
  c.customerid,
  c.name,
  a.countrycode,
  case when c.actorid in 
    (select * from agreement where activeagreement = 'Y' and product in ('6774', '6775'))
   then 1 else 0 end as hasaccount,
  i.controldate,
  i.controlby
from customer c
left join agreement a on a.actorid = c.actorid
left join 
(
  select top 1 with ties *
  from identification
  order by row_number() over (partition by actorid order by controldate desc)
) i on i.actorid = c.actorid
where c.customerstatus = 'Active';

更新:上面的答案不适用于OP，因此我提供了以下两个可行的替代方案：

left join
(
  select
    actorid, controlby, controldate,
    max(controlby) over (partition by actorid) as max_controldate
  from identification
) i on i.actorid = c.actorid and i.controldate = i.max_controldate. 

和

left join
(
  select *
  from identification
  qualify row_number() over (partition by actorid order by controldate desc) = 1)
) i on i.actorid = c.actorid. – Thorsten

使用QUALIFY的最后一个选项是实现这一目的的teradata方法。QUALIFY是对SQL标准的标准的扩展。另外两种方法是标准SQL。

Answer 2

最快的解决方案可能是使用ISNULL。在编写MAX(ControlDate)时，使用MAX(ISNULL(ControlDate,'1970-01-01')) (或任何默认日期)扩展它。

这将取代NULL，并使您的查询工作。

希望能帮上忙。彼得