求助，在线等！

[land_L]

Table1：
ID TIME1 TIME2 NUM
A 11.4 11.20 1000
A 11.5 11.20 800
A 11.6 11.20 2000
A 11.7 11.20 600

想要得到表T2:
ID TIME1 TIME2 before after
A 11.5 11.20 1000 800
A 11.6 11.20 800 2000
A 11.7 11.20 2000 600

得到table1的时候已经order by TIME2,TIME1

现在求助怎么才能得到T2

[Mr_Nice]

if object_id('[T1]') is not null drop table [T1]
create table [T1] (ID varchar(1),TIME1 numeric(3,1),TIME2 numeric(4,2),NUM int)
insert into [T1]
select 'A',11.4,11.20,1000 union all
select 'A',11.5,11.20,800 union all
select 'A',11.6,11.20,2000 union all
select 'A',11.7,11.20,600

select * from [T1]


SELECT A.id,A.time1,A.time2,B.num AS [before],a.num AS [after]
FROM T1 A
inner JOIN T1 B ON A.time1 -0.1  = B.time1

/*
id	time1	time2	before	after
A	11.5	11.20	1000	800
A	11.6	11.20	800	2000
A	11.7	11.20	2000	600*/

[还在加载中灬]

根据你的实际情况，相应的条件需要你自己加，或者说出来

;WITH CTE AS(
	SELECT ROW_NUMBER()OVER(ORDER BY TIME1)RN,* FROM TB
)
SELECT T1.ID,T1.TIME1,T1.TIME2,T2.NUM before,T1.NUM after FROM CTE T1 LEFT JOIN CTE T2 ON T1.RN=T2.RN+1 WHERE T2.ID IS NOT NULL

[KeepSayingNo]

select t2.ID,t2.TIME1,t2.TIME2.t1.NUM as before, t2.NUM as [after] from
(
    select * ,ROW_NUMBER() over (partition by ID order by TIME2,TIME1) as rn 
    from Table1
) t1 inner join
(
    select * ,ROW_NUMBER() over (partition by ID order by TIME2,TIME1) as rn 
    from Table1
) t2 on t1.rn+1=t2.rn

[KeepSayingNo]

1楼有个问题就是步长不一定是0.1

[chen357313771]

引用 3 楼 dotnetstudio 的回复:

select t2.ID,t2.TIME1,t2.TIME2.t1.NUM as before, t2.NUM as [after] from
(
    select * ,ROW_NUMBER() over (partition by ID order by TIME2,TIME1) as rn 
    from Table1
) t1 inner join
(
    select * ,ROW_NUMBER() over (partition by ID order by TIME2,TIME1) as rn 
    from Table1
) t2 on t1.rn+1=t2.rn

+1