日期:2014-05-17 浏览次数:20554 次
declare @t table(t_date date, id int, name nvarchar(30),price money)
insert into @t
select '20130101', 1, N'茄子', 1.2 union
select '20130102', 1, N'茄子', 2.5 union
select '20130101', 2, N'黄瓜', 3 union
select '20130102', 2, N'黄瓜', 2.6 union
select '20130104', 2, N'黄瓜', 5.1 union
select '20130102', 3, N'西红柿', 2.3 union
select '20130103', 4, N'土豆', 1.2 union
select '20130104', 4, N'土豆', 2.3
select A.t_date,A.name,A.price,
(select top 1 price from @t B where name=a.name and t_date<A.t_date order by t_date desc) price_before
from @t A
order by name
declare @T table (loginDate datetime,loginUser varchar(1))
insert into @T
select '2013-04-29 09:11:12','A' union all
select '2013-04-29 11:16:45','A' union all
select '2013-04-29 15:31:18','A' union all
select '2013-04-29 17:57:46','A' union all
select '2013-04-29 20:20:23','A'
;with maco as
(
select row_number() over (order by loginDate) as id, * from @T
)
select
a.*,datediff(mi,b.loginDate,a.loginDate) as DDif
from maco a left join maco b on a.id=b.id+1
/*
id loginDate loginUser DDif
-------------------- ----------------------- --------- -----------
1 2013-04-29 09:11:12.000 A NULL
2 2013-04-29 11:16:45.000 A 125
3 2013-04-29 15:31:18.000 A 255
4 2013-04-29 17:57:46.000 A 146
5 2013-04-29 20:20:23.000 A 143
*/