IF EXISTS(SELECT NAME FROM sys.objects AS o WHERE NAME = 'a') DROP TABLE A GO create table a (tradedate datetime,mid int,ReckonPrice int) insert into a select '2014-04-23',10001,101 union all select '2014-04-24',10001,102 union all select '2014-04-25',10001,103 union all select '2014-04-26',10001,104 GOSELECT * , LAG(ReckonPrice , 1 , 0) OVER(ORDER BY tradedate) FROM A AS a /* tradedate mid ReckonPrice ----------------------- ----------- ----------- ----------- 2014-04-23 00:00:00.000 10001 101 0 2014-04-24 00:00:00.000 10001 102 101 2014-04-25 00:00:00.000 10001 103 102 2014-04-26 00:00:00.000 10001 104 103(4 row(s) affected) */又到了我推销2012的时间了,这个比自连接的效率要高
a.*,b.reckonPrice as lastPrice
from
a left join a as b on a.mid=b.mid and datediff(dd,b.tradedate,a.tradedate)=1
DROP TABLE A
GO
create table a (tradedate datetime,mid int,ReckonPrice int)
insert into a
select '2014-04-23',10001,101
union all select '2014-04-24',10001,102
union all select '2014-04-25',10001,103
union all select '2014-04-26',10001,104
GOSELECT * , LAG(ReckonPrice , 1 , 0) OVER(ORDER BY tradedate) FROM A AS a
/*
tradedate mid ReckonPrice
----------------------- ----------- ----------- -----------
2014-04-23 00:00:00.000 10001 101 0
2014-04-24 00:00:00.000 10001 102 101
2014-04-25 00:00:00.000 10001 103 102
2014-04-26 00:00:00.000 10001 104 103(4 row(s) affected) */又到了我推销2012的时间了,这个比自连接的效率要高