当用户升级后，找他升级前的最后一次订单、和下单时间，如何写SQL
比如下面数据：查询出下面两个记录，即VIP为y前该用户的信息。
A 2011-3 11111d
B 2011-1 22222c
user time dingdan vip
A 2011-1 11111c
A 2011-3 11111d
A 2012-1 11111e y
B 2011-1 22222c
B 2012-1 22222d y
------解决方案--------------------
--try
select * from tb as t
where not exists(select 1 from tb where user=t.user and time>t.time )
and vip !='y'
------解决方案--------------------
select * from tb as t
where not exists(select 1 from tb where user=t.user and time>t.time )
and vip !='y'
------解决方案--------------------
嗯 没有出来，还有一个条件，那就是a可能会变成两次VIP。需要变化前的两次都出来。
为了方便你调试，我写的表。即把下面的出来的弄出来
create table #x(userid varchar(1),riqi datetime,code varchar(3),vip varchar(1))
insert into #x values('a','2011-01-01','11a','')
insert into #x values('a','2011-02-01','11b','')----出来
insert into #x values('a','2011-03-01','11c','y')
insert into #x values('a','2011-04-01','11d','')----出来
insert into #x values('a','2011-05-01','11e','y')
insert into #x values('b','2011-01-01','21a','')----出来
insert into #x values('b','2011-03-01','21b','y')
------解决方案--------------------
select c.* from #x c,(
select a.userid,max(a.riqi) riqi from #x a,
(select userid,riqi from #x where vip='y')b
where a.riqi<b.riqi and a.userid=b.userid
group by b.riqi,a.userid
)d
where c.userid=d.userid and c.riqi=d.riqi
--结果：
userid riqi code vip
a 2011-02-01 00:00:00.000 11b
a 2011-04-01 00:00:00.000 11d
b 2011-01-01 00:00:00.000 21a
试一下