这样结果，SQL语句如何写

[飞猫0213]

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
--想要得到以下结果

/*
tradedate mid reckonPrice lastPrice
'2014-04-23',10001,101 ,0 --上日余额找不到，默认0
'2014-04-24',10001,102 ,101 --23号的reckonPric
'2014-04-25',10001,103 ,102 --24
'2014-04-26',10002,104 ,103 --25
*/
只要一个语句得到,不能用游标实现。因为数据量很大

[shinger126]

select tradedate,mid ,reckonPrice , lastPrice=isnull((select top 1 reckonPrice from a where tradedate<t.tradedate order by tradedate desc),0) from a t

[--小F--]

select 
   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

[cy2889792]

上面两条语句都能实现，但是非常耗资源。但是既然楼主说了数据量大的话用这两条语句不现实，只能在写表的时候就把lastPrice记录下来。 这样就能把资源损耗降到最低。

[yoan2014]

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
GO

SELECT * , 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的时间了，这个比自连接的效率要高