时间戳

首页 > 解决方案 > 基于最佳匹配加入

sql - 基于最佳匹配加入

问题描述

我有这两张表：

Table: Estimates

| EstimateNumber | Quantity | TotalCost |
| -------------- | -------- | --------- |
| 183232         | 2000     | 5890.42   |
| 183232         | 2500     | 6935.63   |
| 183232         | 3500     | 9016.21   |
| 183232         | 3000     | 7980.28   |

Table: Jobs

| JobNumber | EstimateNumber | QuantityOrdered |
| --------- | -------------- | --------------- |
| PK05188   | 183232         | 2500            |
| PK05591   | 183232         | 3000            |

我想要这个：

| Job Number | TotalCost | Other_Info_From_Corresponding_Estimates_Row |
| ---------- | --------- | ------------------------------------------ |
| PK05188    | 6935.63   | 'Example'                                  |
| PK05591    | 7980.28   | 'Example'                                  |

正确的关联成本是数量最接近的成本。最初我正在做这样的事情：

SELECT 

    Estimates.EstimateNumber,
    Estimates.TotalCost

FROM Estimates

INNER JOIN

    (SELECT DISTINCT

        Jobs.EstimateNumber,
        FIRST_VALUE( Estimates.Quantity ) OVER ( PARTITION BY Jobs.EstimateNumber ORDER BY ABS( Jobs.QuantityOrdered - Estimates.Quantity ) ASC ) CorrectQuantity

FROM Jobs

INNER JOIN Estimates

ON Jobs.EstimateNumber = Estimates.EstimateNumber ) AS QuantityTable

ON Estimates.EstimateNumber = QuantityTable.EstimateNumber AND Estimates.Quantity = QuantityTable.CorrectQuantity

然后将其用作子查询并根据相应的 EstimateNumber 匹配 JobNumber。但是我没有意识到虽然 JobNumber 是不同的，但它们有时可能指的是相同的估计值。这会导致可以理解的奇怪行为，其中一项工作将根据基本操作顺序“赢得”最接近的匹配。即使没有这种行为，它最终也会在加入时复制作业。

有什么建议么？

标签： sqlsql-servertsql

解决方案

对于您的示例数据，您可以使用完全匹配：

select j.*, e.*
from jobs j left join
     estimates e
     on j.jobnumber = e.jobnumber and
        j.QuantityOrdered = e.quantity;

但是，您的问题表明可能没有完全匹配。您没有指定定义“最近”的规则，但您可以为此使用横向连接：

select j.*, e.*
from jobs j outer apply
     (select top (1) e.*
      from estimates e
      where j.jobnumber = e.jobnumber and
      order by abs(j.QuantityOrdered - e.quantity)
     ) e

推荐阅读