如何在两个表中配对DateTimes

Question

我正在查看一个包含两个表的SQL Server2008数据库，每个表都有一个PK (INT)列和一个DateTime列。

表之间没有明确的关系，除非我知道应用程序有一种启发式的倾向，即成对地插入到数据库中，每个表中都有一行，其中的DateTimes似乎永远不会完全匹配，但通常非常接近。

我正在尝试通过在另一个表中找到最匹配的DateTime来匹配每个表中的DateTime。每个主键只能使用一次进行此匹配。

做这件事最好的方法是什么？

编辑:对不起，请在底部找到一些示例输入和所需的输出。

+-------+-------------------------+
| t1.PK |       t1.DateTime       |
+-------+-------------------------+
|     1 | 2016-08-11 00:11:03.000 |
|     2 | 2016-08-11 00:11:08.000 |
|     3 | 2016-08-11 11:03:00.000 |
|     4 | 2016-08-11 11:08:00.000 |
+-------+-------------------------+

+-------+-------------------------+
| t2.PK |       t2.DateTime       |
+-------+-------------------------+
|     1 | 2016-08-11 11:02:00.000 |
|     2 | 2016-08-11 00:11:02.000 |
|     3 | 2016-08-11 22:00:00.000 |
|     4 | 2016-08-11 11:07:00.000 |
|     5 | 2016-08-11 00:11:07.000 |
+-------+-------------------------+

+-------+-------+-------------------------+-------------------------+
| t1.PK | t2.PK |       t1.DateTime       |       t2.DateTime       |
+-------+-------+-------------------------+-------------------------+
|     1 |     2 | 2016-08-11 00:11:03.000 | 2016-08-11 00:11:02.000 |
|     2 |     5 | 2016-08-11 00:11:08.000 | 2016-08-11 00:11:07.000 |
|     3 |     1 | 2016-08-11 11:03:00.000 | 2016-08-11 11:02:00.000 |
|     4 |     4 | 2016-08-11 11:08:00.000 | 2016-08-11 11:07:00.000 |
+-------+-------+-------------------------+-------------------------+

Answer 1

联接到t1.DateTime和t2.DateTime之间DATEDIFF最小(以秒为单位)的行。

Answer 2

您可以通过将表1与表2交叉连接，然后根据Tab Alleman的建议获得日期的差值(以秒为单位)，从而实现所需的结果。然后，下一步是使用ROW_NUMBER()函数对每个匹配项进行排名。最后一步是只选择Rank = 1的行。下面的示例演示如何使用示例数据：

DECLARE @t1 TABLE
(
     ID         INT PRIMARY KEY
    ,[DateTime] DATETIME
);

DECLARE @t2 TABLE
(
    ID          INT PRIMARY KEY
    ,[DateTime] DATETIME
)

INSERT INTO @t1
(
     ID         
    ,[DateTime]
)
VALUES
(1 ,'2016-08-11 00:11:03.000'),
(2 ,'2016-08-11 00:11:08.000'),
(3 ,'2016-08-11 11:03:00.000'),
(4 ,'2016-08-11 11:08:00.000');

INSERT INTO @t2
(
     ID         
    ,[DateTime]
)
VALUES
(1, '2016-08-11 11:02:00.000'),
(2, '2016-08-11 00:11:02.000'),
(3, '2016-08-11 22:00:00.000'),
(4, '2016-08-11 11:07:00.000'),
(5, '2016-08-11 00:11:07.000');


WITH CTE_DateDifference
AS
(
    SELECT      t1.ID AS T1_ID
                ,t2.ID AS T2_ID
                ,t1.[DateTime] AS T1_DateTime
                ,t2.[DateTime] AS T2_DateTime
                ,ABS(DATEDIFF(SECOND, t1.[DateTime], t2.[DateTime])) AS Duration    -- Determine the difference between the dates in seconds.
    FROM        @t1 t1
    CROSS JOIN  @t2 t2
),CTE_RankDateMatch
AS
(
    SELECT  T1_ID
            ,T2_ID
            ,T1_DateTime
            ,T2_DateTime
            ,ROW_NUMBER() OVER (PARTITION BY T1_ID ORDER BY Duration) AS [Rank]  -- Rank each match, the row numbers generated will be order based on the duration between the dates.   Thus rows with a number of 1will be the closest match between the two tables.   
    FROM    CTE_DateDifference
)
-- Finally select out the rows with a Rank equal to 1.
SELECT  *   
FROM    CTE_RankDateMatch
WHERE   [Rank] = 1