如何在SQL Server 2005中选择最接近的匹配项？

admin

如何在SQL Server 2005中选择最接近的匹配项？

sql

在SQL Server
2005中，我有一个成功销售的输入表，以及各种表，其中包含有关已知客户及其详细信息的信息。对于每一行销售，我需要匹配0或1个已知客户。

我们从销售表中获得以下信息：
ServiceId，地址，ZipCode，EmailAddress，HomePhone，FirstName，LastName

客户信息包括所有这些信息以及“ LastTransaction”日期。

这些字段中的任何一个都可以映射回0个或多个客户。我们将匹配数视为销售表中的ServiceId，Address +
ZipCode，EmailAddress或HomePhone完全匹配客户的任何时间。

问题在于我们掌握了许多客户的信息，有时在同一家庭中有多个客户。这意味着我们可能在同一屋子里有John Doe，Jane Doe，Jim Doe和Bob
Doe。它们都可以在Address + ZipCode和HomePhone上匹配-并且可能其中一个以上也可以在ServiceId上匹配。

我需要某种方法来优雅地跟踪交易中客户的“最佳”匹配。如果一个匹配6个字段，而另一个仅匹配5个字段，则该客户应保留为该记录的匹配项。如果有多个匹配项5，而没有一个匹配项，则应保留最近的LastTransaction日期。

任何想法将不胜感激。

更新：更清楚一点，我正在寻找一种验证数据行中完全匹配数目的好方法，并根据该信息选择要关联的行。如果姓氏是“
Doe”，则它必须与客户的姓氏完全匹配，才能算作匹配参数，而不是非常接近的匹配项。

阅读 264

2021-07-01

共1个答案

admin

对于SQL Server 2005及更高版本，请尝试：

;WITH SalesScore AS (
SELECT
    s.PK_ID as S_PK
        ,c.PK_ID AS c_PK
        ,CASE 
             WHEN c.PK_ID IS NULL THEN 0
             ELSE CASE WHEN s.ServiceId=c.ServiceId THEN 1 ELSE 0 END
                  +CASE WHEN (s.Address=c.Address AND s.Zip=c.Zip) THEN 1 ELSE 0 END
                  +CASE WHEN s.EmailAddress=c.EmailAddress THEN 1 ELSE 0 END
                  +CASE WHEN s.HomePhone=c.HomePhone THEN 1 ELSE 0 END
         END AS Score
    FROM Sales s
        LEFT OUTER JOIN Customers c ON s.ServiceId=c.ServiceId
                                       OR (s.Address=c.Address AND s.Zip=c.Zip)
                                       OR s.EmailAddress=c.EmailAddress
                                       OR s.HomePhone=c.HomePhone 
)
SELECT 
    s.*,c.*
    FROM (SELECT
              S_PK,MAX(Score) AS Score
              FROM SalesScore 
              GROUP BY S_PK
         ) dt
        INNER JOIN Sales          s ON dt.s_PK=s.PK_ID 
        INNER JOIN SalesScore    ss ON dt.s_PK=s.PK_ID AND dt.Score=ss.Score
        LEFT OUTER JOIN Customers c ON ss.c_PK=c.PK_ID

编辑
我不愿在没有shema的情况下编写太多实际代码，因为我无法实际运行此代码并确保它能正常工作。但是，为了回答如何使用最后交易日期处理关系的问题，这是上述代码的较新版本：

;WITH SalesScore AS (
SELECT
    s.PK_ID as S_PK
        ,c.PK_ID AS c_PK
        ,CASE 
             WHEN c.PK_ID IS NULL THEN 0
             ELSE CASE WHEN s.ServiceId=c.ServiceId THEN 1 ELSE 0 END
                  +CASE WHEN (s.Address=c.Address AND s.Zip=c.Zip) THEN 1 ELSE 0 END
                  +CASE WHEN s.EmailAddress=c.EmailAddress THEN 1 ELSE 0 END
                  +CASE WHEN s.HomePhone=c.HomePhone THEN 1 ELSE 0 END
         END AS Score
    FROM Sales s
        LEFT OUTER JOIN Customers c ON s.ServiceId=c.ServiceId
                                       OR (s.Address=c.Address AND s.Zip=c.Zip)
                                       OR s.EmailAddress=c.EmailAddress
                                       OR s.HomePhone=c.HomePhone 
)
SELECT
    *
    FROM (SELECT 
              s.*,c.*,row_number() over(partition by s.PK_ID order by s.PK_ID ASC,c.LastTransaction DESC) AS RankValue
              FROM (SELECT
                        S_PK,MAX(Score) AS Score
                        FROM SalesScore 
                        GROUP BY S_PK
                   ) dt
                  INNER JOIN Sales          s ON dt.s_PK=s.PK_ID 
                  INNER JOIN SalesScore    ss ON dt.s_PK=s.PK_ID AND dt.Score=ss.Score
                  LEFT OUTER JOIN Customers c ON ss.c_PK=c.PK_ID
         ) dt2
    WHERE dt2.RankValue=1

2021-07-01