有張表 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,小弟怎么都寫不出來。。。。
1 回答
冉冉說
TA貢獻1877條經驗 獲得超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]
添加回答
舉報
0/150
提交
取消