有張表 type value time2 5 12:424 -42 13:192 2 14:482 7 12:543 16 13:193 20 15:01 需要按照type排序，返回同一個(gè)type的，最近時(shí)間和次近時(shí)間的兩個(gè)value的差值比如 type為2的，最近時(shí)間是 14:48，value為2 ，接下來(lái)時(shí)間是 12:54,value為7所以差值為 2-7 = -5 所以結(jié)果是 type value2 -53 4 求高手幫忙寫SQL,小弟怎么都寫不出來(lái)。。。。
1 回答
冉冉說(shuō)
TA貢獻(xiàn)1877條經(jīng)驗(yàn) 獲得超1個(gè)贊
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]
添加回答
舉報(bào)
0/150
提交
取消