SQL Server：使用Join/Cross中的记录只应用一次

Question

我希望我的Cross应用程序删除记录，如果它已加入，并且不考虑将记录加入其他记录中。是否可以在一个声明中做到这一点？

因此，在下面的示例中，期望的结果是对于第二条记录具有MatchID = 1，因为B.recordID = 2已被第一条记录占用。我相信，示例代码将明确：

http://sqlfiddle.com/#!6/555a1/5/0

因此，在总结，我需要在表B中的记录不被视为可用了，如果他们使用的匹配。

EDIT1：添加示例代码：

构建架构：

CREATE TABLE A 
(
    id int, 
    eventName varchar(80), 
    startdateTime datetime, 
    duration int, 
    MatchedID int NULL 
) 

CREATE TABLE B 
(
    id int, 
    eventName varchar(80), 
    startdateTime datetime, 
    duration int 
) 

INSERT INTO A 
    SELECT 1, 'e1', dateadd(day, -1, getdate()), 5, NULL 
    UNION 
    SELECT 2, 'e1', dateadd(hour, -1, getdate()), 5, NULL 

INSERT INTO B 
    SELECT 1, 'e1', dateadd(day, -1, getdate()), 4 
    UNION 
    SELECT 2, 'e1', dateadd(hour, -1, getdate()), 5 

查询：

UPDATE toUpdate 
SET toUpdate.MatchedID = Tbtmp.id 
FROM A toUpdate 
CROSS APPLY (SELECT TOP(1) * 
      FROM B WHERE toUpdate.eventName = B.eventName 
      ORDER BY ABS(toUpdate.Duration - B.Duration)) Tbtmp; 

SELECT * FROM A 

结果：

| id | eventName |   startdateTime | duration | MatchedID | 
+----+-----------+--------------------------+----------+-----------+ 
| 1 |  e1 | 2017-10-11T11:34:15.083Z |  5 |   2 | 
| 2 |  e1 | 2017-10-12T10:34:15.083Z |  5 |   2 | 

预期结果（MatchedID对于第二REC ORD应该是1，因为2已被使用，即使是接近原始记录）

| id | eventName |   startdateTime | duration | MatchedID | 
+----+-----------+--------------------------+----------+-----------+ 
| 1 |  e1 | 2017-10-11T11:34:15.083Z |  5 |   2 | 
| 2 |  e1 | 2017-10-12T10:34:15.083Z |  5 |   1 | 

Answer 1

你可以试试这个？为了删除重复的记录（因为eventName加入），我使用额外的CTE。我用您的示例数据对它进行了测试，它似乎正在工作。我希望它也适用于您的真实数据。

;WITH toUpdate AS 
(
    SELECT *, 
     RN_Update = ROW_NUMBER() OVER(PARTITION BY A.eventName ORDER BY A.id) 
    FROM A 
) 
, fromSource AS 
(
    SELECT B.*, 
     A.RN_Update, 
     RN_Source = ROW_NUMBER() OVER(PARTITION BY A.eventName, A.id ORDER BY A.id, ABS(A.Duration - B.Duration)) 
    FROM B 
     INNER JOIN toUpdate A ON A.eventName = B.eventName 
) 
UPDATE toUpdate 
SET 
    MatchedID = fromSource.id 
FROM toUpdate 
    INNER JOIN fromSource ON toUpdate.eventName = fromSource.eventName 
       AND toUpdate.RN_Update = fromSource.RN_Source 
       AND fromSource.RN_Update = fromSource.RN_Source 

结果：

id   eventName startdateTime   duration MatchedID 
----------- ----------- ----------------------- ----------- ----------- 
1   e1   2017-10-11 15:33:11.890 5   2 
2   e1   2017-10-12 14:33:11.890 5   1