sql - 仅匹配 JOIN 中存在许多行的特定行

Question

(Advantage Database Server) 我有一个服务提供商表，出于审计目的，这些服务提供商永远不会被删除。他们有开始日期和结束日期；对于名称或地址等更改，现有行的结束日期将被创建，新行将被创建，并为更改的数据分配一个新的开始日期。

在处理向这些提供者付款的过程中，我需要一个摘要页面，其中列出提供者名称、地址、标识符 (ProvID) 和支付的总金额。这是在使用 SUM() 和 GROUP BY 的相当简单的查询中完成的。

当指定的提供者标识符有两行或更多行时，就会出现问题。我最终得到了重复的行(如果没有被捕获，可能会导致向该提供商多次付款)。

我的第一个想法是使用像子选择这样的东西(丑陋，但执行速度相当快):

SELECT ... FROM service s
INNER JOIN provider p ON p.ProvID = s.ProvID
AND (p.EndDate IS NULL or p.EndDate = (SELECT Max(EndDate) FROM
   provider lu WHERE lu.ProvID = s.ProvID))

不幸的是，这最终还是找到了两行；一行用于 NULL EndDate，另一行用于 MAX(EndDate)。

我在其他情况下处理这个问题(例如，为在特定日期提供的服务找到正确的 ProvID)使用

p.EndDate is null or (s.ServiceDate BETWEEN p.StartDate AND p.EndDate)

不幸的是，由于问题查询是带有聚合的 GROUP BY，因此服务日期不可用。

有什么建议吗？

编辑:我正在寻找的是带有 NULL EndDate 的行(如果它存在)或者带有 Max(EndDate) 的行(如果 NULL 行不存在)。这包括这样的情况，例如，供应商昨天被终止，但上周确实工作，我们将在下周向他们付款。

Answer 1

所以我想如果有一行结束日期为 NULL，您想要那一行，否则您想要结束日期最大的那一行？

我不确定 ADS，但以下内容适用于 SQL Server:

SELECT ... FROM service s
INNER JOIN provider p ON p.ProvID = s.ProvID
AND (COALESCE(p.EndDate, '2037-01-01') = (
   SELECT Max(COALESCE(EndDate, '2037-01-01')) FROM
   provider lu WHERE lu.ProvID = s.ProvID)
)

COALESCE运算符返回第一个非 null 参数，所以这基本上只是将 null 设置为 future 的某个时间，这样 SELECT MAX 将为您提供结束日期为 NULL 的参数(如果有的话)。