如何选择SQL Server 2005中最近的匹配项?

对于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

这是一种相当丑陋的方法,使用SQL Server代码。假设:
-customerID列存在于customer表中,以唯一标识客户。
-只支持完全匹配(如问题所暗示的那样)。

SELECT top 1 CustomerId, LastTransaction, count(*) HowMany
 from (select Customerid, LastTransaction
        from Sales sa
         inner join Customers cu
          on cu.ServiceId = sa.ServiceId
       union all select Customerid, LastTransaction
        from Sales sa
         inner join Customers cu
          on cu.EmailAddress = sa.EmailAddress
       union all select Customerid, LastTransaction
        from Sales sa
         inner join Customers cu
          on cu.Address = sa.Address
           and cu.ZipCode = sa.ZipCode
       union all [etcetera -- repeat for each possible link]
      ) xx
 group by CustomerId, LastTransaction
 order by count(*) desc, LastTransaction desc

我不喜欢用“第一名”,但写起来更快。(另一种选择是使用排名函数,这将需要另一个子查询级别或将其作为CTE来实现。)当然,如果您的表很大,除非您的所有列上都有索引,否则这将像牛一样飞起来。

坦率地说,我完全不会这么做,因为您的数据中没有唯一的标识符。

约翰·史密斯和他的儿子约翰·史密斯住在一起,他们都使用相同的电子邮件地址和家庭电话。这是两个人,但你会把他们当作一个。我们总是在使用数据时遇到这种情况,因此没有自动匹配的解决方案。我们识别出可能的重复,实际打电话,找出他们是重复。

我可能会为它(在Oracle中)和最高匹配的ORD创建一个存储函数

SELECT * FROM (
 SELECT c.*, MATCH_CUSTOMER( Customer.Id, par1, par2, par3 ) matches FROM Customer c
) WHERE matches >0 ORDER BY matches desc

函数match_customer根据输入参数返回匹配数…我想可能是慢了,因为这个查询总是扫描完整的customer表

对于相近的匹配,您还可以查看一些字符串相似性算法。

例如,在Oracle中有utl_match.jaro_winkler_相似性函数:
http://www.psoug.org/reference/utl_match.html

还有 Levenshtein distance algorithym .