紧急求助SQL 面试题

Question

有张表 type  value  time2       5        12:424       -42     13:192        2        14:482        7       12:543        16     13:193        20     15:01 需要按照type排序，返回同一个type的，最近时间和次近时间的两个value的差值比如 type为2的，最近时间是 14:48，value为2  ，接下来时间是 12:54,value为7所以差值为 2-7 = -5 所以结果是 type  value2      -53       4 求高手帮忙写SQL,小弟怎么都写不出来。。。。

Answer 1

create table test5
(
[type] int not null,
[value] int not null,
[time] time
)

go

INSERT 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')

go

with 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]