在存在许多行的 JOIN 中仅匹配一个特定行

Question

（优势数据库服务器）我有一个服务提供商表，出于审计目的，该表永远不会被删除。他们有开始日期和结束日期；如果发生名称或地址等更改，则会在现有行中添加结束日期，创建新行，并为更改的数据分配新的开始日期。

在处理向这些提供商付款的过程中，我需要一个摘要页面，其中列出提供商名称、地址、标识符 (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 运算符返回第一个非空参数，因此这基本上只是将空值设置为遥远的未来时间，以便 SELECT MAX 将为您提供带有 NULL 结束日期的参数（如果有）。

Answer 2

在第二个条件中，只有在没有 NULL EndDate 时才必须获取最大值

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)
         AND NOT EXISTS (SELECT NULL 
                           FROM provider lu 
                          WHERE lu.ProvID = s.ProvID 
                            AND lu.EndDate IS NULL)
        )
    )

Answer 3

也许使用子查询代替第二个表：

SELECT ... FROM service s
INNER JOIN (SELECT ..., Max(EndDate) FROM
   provider lu WHERE lu.ProvID = s.ProvID GROUP BY ...) p ON p.ProvID = s.ProvID

这是假设如果没有最大结束日期，您将得到 NULL。

Answer 4

您指的是数据仓库的 Type-2 维度。

您必须通过 ID 和通过 StartDate 和 EndDate 加入才能获取正确的数据。

奥托姆代码

SELECT TransactionId, TransactionType
FROM TransactionList Tx
    INNER JOIN TransactionType TxType
        ON Tx.TransactionTypeId = TxType.TxTypeId
        AND Tx.TransactionDate Between TxType.StartDate and TxType.EndDate

Answer 5

您的提供商表中的什么表示当前日期？ EndDate=NULL、EndDate=Max(EndDate) 或 EndDate='9999-01-01'？这三个都是有效的选择，但这应该是明确的，因为如果不是这样，无论您如何巧妙地设计这个特定的查询，您都会在查询中始终得到重复的行。所以我建议在提供者表中修复这个问题，然后类似这样的事情应该可以工作：

select p.name, p.address, p.id, sum(s.amount)
  from provider p
  join service s on p.id=s.provider_id
where p.endDate is NULL
group by p.name, p.address, p.id