匹配来自两个具有多个相同记录的表的记录(Match records from two tables that have multiple identical records)

我有两个表，我需要根据标准验证/匹配完全匹配的记录。在某些情况下，同一个表中可能有两个相同的记录与第二个表中的两个记录匹配。我遇到的问题是从第一个表中获取不同的recordID以使其与第二个表中的相应记录相匹配。

这是我的查询：

SELECT DISTINCT s.ID, AMT.ID, AMT.VerifiedSalesRecordID AS MatchID, AMT.ProductID FROM AccountManagerTracker AS AMT JOIN Sales AS s ON CHECKSUM (AMT.CompanyID, AMT.DateFulfilled, AMT.ProductID, AMT.GrossSalesAmount, AMT.NetSalesAmount, DATEPART(MM,AMT.CommissionMonth)) = CHECKSUM(s.CompanyID, s.DateFulfilled, s.ProductID, s.GrossSalesAmount, s.NetSalesAmount, DATEPART(MM,s.CommissionMonth)) WHERE s.ProductID IN (1,2,3,38) AND AMT.VerifiedSalesRecordID IS NULL

结果：

T1ID T2ID MatchID ProductID 14777 1 NULL 1 14813 2 NULL 1 14825 4 NULL 1 14933 6 NULL 3 14948 13 NULL 1 14948 14 NULL 1 15029 19 NULL 2 15043 17 NULL 2 15058 18 NULL 2 15151 22 NULL 1 15154 20 NULL 1 15156 25 NULL 2 15328 33 NULL 2 15330 30 NULL 1 15330 31 NULL 1

我只想要来自T1的不同记录。有任何想法吗？

编辑 15330 30 NULL 1 15330 31 NULL 1这两行是问题所在。它试图用表2中的两个不同记录验证表1的相同记录。

I have two tables that I need to verify/match records that match exactly based on criteria. In some cases there can be two identical records in the same table that match to two records in the second table. The problem I'm having is getting the distinct recordID from the first table to match it with the corresponding record in the second table.

Here is my query:

Results:

I only want distinct records from T1. Any ideas?

Edit 15330 30 NULL 1 15330 31 NULL 1 These two rows are the problem. It trys to verify the same record of Table 1 with two different records of Table 2.

最满意答案

像这样的东西：

UPDATE AccountManagerTracker SET VerifiedSalesRecordID = ( SELECT s.ID FROM AccountManagerTracker JOIN Sales AS s ON CHECKSUM (AccountManagerTracker .CompanyID, AccountManagerTracker .DateFulfilled, AccountManagerTracker .ProductID, AccountManagerTracker .GrossSalesAmount, AccountManagerTracker .NetSalesAmount, DATEPART(MM,AMT.CommissionMonth)) = CHECKSUM(s.CompanyID, s.DateFulfilled, s.ProductID, s.GrossSalesAmount, s.NetSalesAmount, DATEPART(MM,s.CommissionMonth)) WHERE s.ProductID IN (1,2,3,38) AND AccountManagerTracker.VerifiedSalesRecordID IS NULL )

您的FROM和JOIN正在为您提供正确的数据。您可以通过结果集看到数据是您想要的。由于您具有正确的s.ID，因此您可以更新VerifiedSalesRecordID列。

如果您正在使用SQL Server，请确保在UPDATE调用之前使用BEGIN TRANSACTION ....这将使数据保持在未提交状态，以便您可以验证UPDATE的结果，并将结果提交给数据库

I actually found a better solution to the problem. I needed record 1 of table a to match with record 1 of table b and record 2 with record 2 etc. Here is the solution I was finally able to come up with.

UPDATE Table1 SET VerifiedSalesRecordID = t2.ID FROM (SELECT AMT.AMID, AMT.CompanyID, AMT.DateFulfilled, AMT.ProductID, AMT.GrossSalesAmount, AMT.NetSalesAmount, AMT.CommissionMonth, AMT.ID, AMT.VerifiedSalesRecordID, ROW_NUMBER() OVER (PARTITION BY AMT.AMID, AMT.CompanyID, AMT.ProductID, AMT.DateFulfilled ORDER BY AMT.CompanyID) AS RecordNum FROM Table1 AS AMT INNER JOIN ( SELECT AMID, CompanyID, DateFulfilled, ProductID, GrossSalesAmount, NetSalesAmount, COUNT(*) AS DupCount FROM Table1 GROUP BY AMID, CompanyID, DateFulfilled, ProductID, GrossSalesAmount, NetSalesAmount ) AS Dup ON AMT.AMID = Dup.AMID AND AMT.CompanyID = Dup.CompanyID AND AMT.DateFulfilled = Dup.DateFulfilled AND AMT.ProductID = Dup.ProductID AND AMT.GrossSalesAmount = Dup.GrossSalesAmount) AS t1 JOIN (SELECT s.SalesRepID, s.CompanyID, s.DateFulfilled, s.ProductID, s.GrossSalesAmount, s.NetSalesAmount, s.CommissionMonth, s.ID, s.Verified, ROW_NUMBER() OVER (PARTITION BY s.SalesRepID, s.CompanyID, s.ProductID, s.DateFulfilled ORDER BY s.CompanyID) AS RecordNum FROM Table2 AS s INNER JOIN ( SELECT SalesRepID, CompanyID, DateFulfilled, ProductID, GrossSalesAmount, NetSalesAmount, COUNT(*) AS DupCount FROM Table2 WHERE SalesTypeID = 2 GROUP BY SalesRepID, CompanyID, DateFulfilled, ProductID, GrossSalesAmount, NetSalesAmount ) AS Dup ON s.SalesRepID = Dup.SalesRepID AND s.CompanyID = Dup.CompanyID AND s.DateFulfilled = Dup.DateFulfilled AND s.ProductID = Dup.ProductID AND s.GrossSalesAmount = Dup.GrossSalesAmount) AS t2 ON t1.AMID = t2.SalesRepID AND t1.CompanyID = t2.CompanyID AND t1.DateFulfilled = t2.DateFulfilled AND t1.ProductID = (CASE WHEN t2.ProductID = 38 THEN t1.ProductID ELSE t2.ProductID END) AND t1.NetSalesAmount = t2.NetSalesAmount AND (CASE WHEN t1.ProductID = 3 THEN t2.GrossSalesAmount ELSE t1.GrossSalesAmount END) = t2.GrossSalesAmount AND DATEPART(MM,t1.CommissionMonth) = DATEPART(MM, t2.CommissionMonth) AND t1.RecordNum = t2.RecordNum WHERE Table1.ID = t1.ID AND t1.VerifiedSalesRecordID IS NULL AND t2.Verified = 0

更多推荐