使用匹配的多列(SQL)查找最近的时间

Question

我有两个表:表1是在某一时间、日期、名称进入一栋建筑物的通道。

Accesses

    |  Date      |    Time      | Name | accesses|
    -----------------------------------------------
    | 2018-10-10 |  10:10:34.00 |  JA  |     1   | 
    | 2018-10-10 |  10:14:10.10 |  AA  |     1   |
    | 2018-10-10 |  12:15:00.45 |  BE  |     1   |
    | 2018-10-10 |  15:00:00.50 |  JA  |     1   |
    | 2018-10-10 |  16:56:56.15 |  BE  |     1   |

table2显示在特定访问过程中是否存在故障。

Failure

    |  Date      |    Time      | Name | failure |
    -----------------------------------------------
    | 2018-10-10 |  10:10:40.00 |  JA  |     1   | 
    | 2018-10-10 |  10:15:06.00 |  AA  |     1   |
    | 2018-10-10 |  16:57:01.14 |  BE  |     1   |

期望输出

   Output

    |  Date      |    Time      | Name | accesses|  Failure |
    ---------------------------------------------------------
    | 2018-10-10 |  10:10:34.00 |  JA  |     1   |    1     |
    | 2018-10-10 |  10:14:10.10 |  AA  |     1   |    1     |
    | 2018-10-10 |  12:15:00.45 |  BE  |     1   |   NULL   |  
    | 2018-10-10 |  15:00:00.50 |  JA  |     1   |   NULL   |
    | 2018-10-10 |  16:56:56.15 |  BE  |     1   |    1     |

基本上，输出将包含访问，并将故障表与从访问表中检测到的最近时间匹配。我尝试了不同的算法来匹配时间戳，但由于不是所有的访问都失败了，所以仍然会出现错误，这就是为什么我希望它在输出中带有NULL。谢谢

Answer 1

数据库在时间表示方式上存在差异。但是，一般的想法是您可以使用exists来处理这个问题。

select a.*,
       (case when exists (select 1
                          from failures f
                          where f.name = a.name and
                                f.date = a.date and
                                f.time > a.time - interval '1' minute and
                                f.time < a.time + interval '1' minute 
             then 1 else 0
        end) as failure_flag
from accesses a;

这使用了一个带有0和1值的标志。显然，您可以删除else 0子句以获得NULL而不是0。

如果将日期和时间结合起来，这实际上会更准确。根据数据的性质，在午夜添加补丁可能不值得。

Answer 2

从注释来看，这个连接条件似乎可以工作。

select distinct
    a.*
    ,Failure = case when f.Name is not null then 1 else null end
from Accesses a
left join Failure F on 
    f.Name = a.Name 
    and f.Time > dateadd(minute,-1, a.Time)
    and f.Time < dateadd(minute, 1, a.Time)
    and f.Date = a.Date 

这里有一种方法，通过在你的独立列中制作一个DATETIME来处理午夜。注意，我为每个表添加了一个额外的行。

declare @Accesses table (Date date, Time time, Name char(2), accesses bit)
insert into @Accesses
values
('2018-10-10','10:10:34.00','JA',1 ),
('2018-10-10','10:14:10.10','AA',1),
('2018-10-10','12:15:00.45','BE',1),
('2018-10-10','15:00:00.50','JA',1),
('2018-10-10','16:56:56.15','BE',1),
('2018-10-10','23:59:56.15','XX',1)

declare @Failure table (Date date, Time time, Name char(2), failure bit)
insert into @Failure
values
('2018-10-10','10:10:40.00','JA',1), 
('2018-10-10','10:15:06.00','AA',1),
('2018-10-10','16:57:01.14','BE',1),
('2018-10-11','00:00:01.15','XX',1)



select distinct
    a.*
    ,Failure = case when f.Name is not null then 1 else null end
from @Accesses a
left join @Failure F on 
    f.Name = a.Name 
    and cast(convert(varchar,f.date) + ' ' + left(convert(varchar,f.time),8) as datetime) > dateadd(minute,-1,cast(convert(varchar,a.date) + ' ' + left(convert(varchar,a.time),8) as datetime))
    and cast(convert(varchar,f.date) + ' ' + left(convert(varchar,f.time),8) as datetime) < dateadd(minute,1,cast(convert(varchar,a.date) + ' ' + left(convert(varchar,a.time),8) as datetime))