sql-server - 使用内连接更新最接近日期时间匹配的查询

Question

我有以下两个表

CREATE TABLE Ep
    ([E] varchar(9), [M] varchar(9), [DTE] DATETIME)
;

INSERT INTO Ep
    ([E], [M], [DTE])
VALUES
    ('1595861-1', '1595861-1', CONVERT(datetime, '2002-11-26 14:18:00', 20)),
    ('1595904-1', '1595904-1', CONVERT(datetime, '2002-11-24 15:15:00', 20)),
    ('1596298-1', '1596298-1', CONVERT(datetime, '2002-12-17 11:12:00', 20)),
    ('1596357-1', '1596357-1', CONVERT(datetime, '2002-12-09 19:57:00', 20)),
    ('1596369-1', '1596369-1', CONVERT(datetime, '2002-12-11 06:00:00', 20)),
    ('1596370-1', '1596370-1', CONVERT(datetime, '2002-12-19 12:31:00', 20)),
    ('1596473-2', '1596473-1', CONVERT(datetime, '2002-12-15 08:39:00', 20)),
    ('1596473-3', '1596473-1', CONVERT(datetime, '2002-12-20 08:39:00', 20)),
    ('1596473-4', '1596473-1', CONVERT(datetime, '2002-12-13 08:39:00', 20)),
    ('1596473-5', '1596473-1', CONVERT(datetime, '2002-12-16 08:39:00', 20)),
    ('1596473-1', '1596473-1', CONVERT(datetime, '2002-12-14 08:39:00', 20))
;

CREATE TABLE Mp
    ([E] varchar(9), [M] varchar(9), [DTE] DATETIME)
;

INSERT INTO Mp
    ([E], [M], [DTE])
VALUES
    ('', '1595861-1', CONVERT(datetime, '2002-11-26 14:18:00', 20)),
    ('', '1595904-1', CONVERT(datetime, '2002-11-24 15:15:00', 20)),
    ('', '1596298-1', CONVERT(datetime, '2002-12-17 11:12:00', 20)),
    ('', '1596357-1', CONVERT(datetime, '2002-12-09 19:57:00', 20)),
    ('', '1596369-1', CONVERT(datetime, '2002-12-11 06:00:00', 20)),
    ('', '1596370-1', CONVERT(datetime, '2002-12-19 12:31:00', 20)),
    ('', '1596473-1', CONVERT(datetime, '2002-12-17 08:39:00', 20))
;

目前我正在更新[E] Mp 中的字段通过 [M] 上的匹配表其中DTE字段(在 Mp 中)在某个范围内(例如 +-3 天)。当前执行此操作的查询是

UPDATE [Mp] 
SET [E] = [Ep].[E] 
FROM [Mp] INNER JOIN [Ep] 
    ON [Mp].[M] = [Ep].[M] 
WHERE [Mp].[DTE] BETWEEN [Ep].[DTE] - 3 AND [Ep].[DTE] + 3;

此更新[Mp].[E]对于 [Mp].[M] = N'1596473-1'至1596473-2 。本质上，SQL Server 发现的第一个条目是有效的。但是，我想更新此查询，以便 SQL Server 在 [M] 上匹配所需日期范围内的字段(就像现在一样)，但对于 [Ep].[DTE]最接近 [Mp].[DTE] 中的值2002-12-17 08:39:00 的值.

我考虑过添加 DATEDIFF子句，按以下方式

UPDATE [Mp] 
SET [E] = [Ep].[E] 
FROM [Mp] INNER JOIN [Ep] 
    ON [Mp].[M] = [Ep].[M] 
WHERE [Mp].[DTE] BETWEEN [Ep].[DTE] - 3 AND [Ep].[DTE] + 3 
ORDER BY DATEDIFF(minutes, [Mp].[DTE], [Ep].[DTE]);

显然我不能这样做，但我不确定如何修改它以使其起作用。更新后[Mp]的最终数据应该是

  1595861-1     1595861-1   2002-11-26 14:18:00.000 
  1595904-1     1595904-1   2002-11-24 15:15:00.000 
  1596298-1     1596298-1   2002-12-17 11:12:00.000 
  1596357-1     1596357-1   2002-12-09 19:57:00.000 
  1596369-1     1596369-1   2002-12-11 06:00:00.000 
  1596370-1     1596370-1   2002-12-19 12:31:00.000 
**1596473-5**   1596473-1   2002-12-17 08:39:00.000 

感谢您的宝贵时间。

Answer 1

请首先检查 CTE 后生成哪些数据作为输出

对于最近的数据，我使用 SQL ROW_NUMBER function with Partition By 子句根据 DATEDIFF() 函数获取的时间差计算。为了消除之前和之后的记录，我使用了 ABS() 数学函数。

select
*,
ROW_NUMBER() over (partition by [Mp].[M] order by abs(datediff(mi, [Mp].[DTE], [Ep].[DTE]))) as rn,
abs(datediff(mi, [Mp].[DTE], [Ep].[DTE])) diff
from Mp
left join Ep 
    on [Mp].[M] = [Ep].[M] 
WHERE [Mp].[DTE] BETWEEN dateadd(dd,-3,[Ep].[DTE]) AND dateadd(dd,3,[Ep].[DTE])

然后在 UPDATE 命令中使用相同的 CTE 表达式，如下所示，您可以将所需的数据填充到目标数据库表中

;with cte as (
    select
        [Mp].[M] as M,
        [Ep].E as E,
    ROW_NUMBER() over (partition by [Mp].[M] order by abs(datediff(mi, [Mp].[DTE], [Ep].[DTE]))) as rn,
    abs(datediff(mi, [Mp].[DTE], [Ep].[DTE])) diff
    from Mp
    left join Ep 
        on [Mp].[M] = [Ep].[M] 
    WHERE [Mp].[DTE] BETWEEN dateadd(dd,-3,[Ep].[DTE]) AND dateadd(dd,3,[Ep].[DTE])
)
update [Mp]
set [E] = cte.E
from [Mp]
inner join cte on [Mp].M = cte.M and cte.rn = 1
where 
    cte.E is not null

执行UPDATE语句后，目标表数据如下

我希望这是您所需要的