sql - SQL 选择其中两个不同的名称在一定时间内彼此

Question

嗨，我只需要在特定时间范围内找到两个名称的情况下返回值

Primary Start_Time            NAME
A12345  22:00:00  FIRST
A12345 22:05:00  SECOND
A12345 21:00:00  FIRST
A12345 21:10:00  SECOND
A54321 21:00:00 FIRST
A54321 21:01:00 SECOND

在这种情况下，如果我们说他们需要返回 FIRST 和 SECOND 彼此在 5 分钟内发生的值，并且它们都具有相同的 Primary ID

SELECT    Table1.Primary,  Table1.START_TIME,  Table2.NAME
FROM            Table1 INNER JOIN
                        Table2 ON Table1.Table2_KEY = Table2.LINK
Where (Table2.NAME = 'First') or (Table2.NAME = 'Second')

标签： sql

Answer 1

考虑 SQL LAG 函数。以下示例概括了您的问题，使其对更广泛的受众更加清晰和有用。

假设您连接的结果集名为“t”。以下示例为您提供了获得答案所需的内容...

select 
    *
    ,lag([Start_Time],1) over (partition by [Primary] order by [Primary],[Start_Time]) as PriorTime
    ,lag([Name],1) over (partition by [Primary] order by [Primary],[Start_Time]) as PriorName
from t

这将返回：

希望您可以从那里开始...即过滤时间差可以接受的小行，但这是下一步的一个示例...

 select 
     *
    ,case when PriorName = 'FIRST' and [Name] = 'SECOND' then datediff(s,[Start_Time],[PriorTime]) else null end as TimeDiff
from
(
    select 
        *
        ,lag([Start_Time],1) over (partition by [Primary] order by [Primary],[Start_Time]) as PriorTime
        ,lag([Name],1) over (partition by [Primary] order by [Primary],[Start_Time]) as PriorName
    from t
) src

...这给了你...