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