sql - 按日期选取连续的两条记录，从数据库中取出记录，日期相差最大

Question

我想按日期选择两条连续的记录，从数据库中的记录，连续日期相差最大。

有一张 table

name dateabc  1242bcd  1246bsd  1247bse  1249

The result of the query should be:

abc  1242bcd  1246

My query is:

    select t1.name, t2.name, max(t2.date - t1.date)
    from temp t1
    join temp t2 on t1.date < t2.date
    where
    not exists (
        select t3.date from temp t3 where t3.date > t1.date and t3.date < t2.date
    )

这是最好的解决方案吗？

提前致谢

Answer 1

declare @a table(name varchar(10), date int)
insert into @a Select 'abc',  1242
union Select 'bcd',  1246
union Select 'bsd',  1280
union Select 'bse',  1242

Select row_number() OVER (ORDER BY name) AS 'RowNumber',*  into #temp from @a

select Top 1 * into #tbl from
(
select a1.Name as N1,a2.Name as N2,abs(a2.date-a1.date) as diff
 from #temp a1 Join #temp a2 on a2.rownumber-1  = a1.rownumber
 )as tbl order by diff desc

select * from @a where name =(select N1 from #tbl) or name =(select N2 from #tbl)

Drop table #temp
Drop table #tbl