紧急求助SQL 面试题

Question

有张表 

type  value  time

2       5        12:42

4       -42     13:19

2        2        14:48

2        7       12:54

3        16     13:19

3        20     15:01

 

需要按照type排序，返回同一个type的，最近时间和次近时间的两个value的差值

比如 type为2的，最近时间是 14:48，value为2  ，接下来时间是 12:54,value为7

所以差值为 2-7 = -5

 

所以结果是 

type  value

2      -5

3       4

 

求高手帮忙写SQL,小弟怎么都写不出来。。。。

Answer 1

create table test5([type] int not null,[value] int not null,[time] time)goINSERT INTO test5 VALUES(2,5,'2:42:00')INSERT INTO test5 VALUES(4,-42,'3:42:00')INSERT INTO test5 VALUES(2,2,'4:42:00')INSERT INTO test5 VALUES(2,7,'2:52:00')INSERT INTO test5 VALUES(3,16,'2:42:00')INSERT INTO test5 VALUES(3,20,'3:42:00')gowith v1 as (select * from (select top 100 *, row_number() over ( partition by [type] order by time) as [rank] from test5) V1 where V1.rank=1),v2 as (select * from (select top 100 *, row_number() over ( partition by [type] order by time) as [rank] from test5) V1 where V1.rank=2)select v1.[type],v1.[value]-v2.[value] from v1 left join v2 on v1.[type]=v2.[type]