update BIDS set seller_class=(select top 1 class_new from userclassinfo where user_id=bids.seller_id and change_class_time<=bids.bid_time order by change_class_time desc),
buyer_class=(select top 1 class_new from userclassinfo where user_id=bids.buyer_id and change_class_time<=bids.bid_time order by change_class_time desc)
buyer_class=(select top 1 class_new from userclassinfo where user_id=bids.buyer_id and change_class_time<=bids.bid_time order by change_class_time desc)
create table #userclassinfo(user_id int,changer_class_time datetime,class_old int, class_new int )insert #userclassinfo select 1598, '2002-4-1 10:00:00', 1, 1
union select 2436, '2002-5-1 18:34:21', 1, 1
union select 2590, '2002-5-5 13:00:00', 1, 1
union select 3176, '2002-6-12 9:20:00', 1, 1
union select 3176, '2002-10-2 8:00:00', 1, 2
union select 2590, '2002-12-28 6:00:00', 1, 2
union select 2436, '2003-1-10 10:00:20', 1, 2
union select 1598, '2003-2-6 8:00:00', 1, 2
union select 3176, '2003-2-7 6:30:00', 2, 3
union select 2590, '2003-2-8 7:20:00', 2, 1create table #bids(bid_id int, seller_id int, buyer_id int, bid_time datetime, seller_class int, buyer_class int)
insert #bids (bid_id,seller_id,buyer_id,bid_time) select 52367, 3176, 1598, '2003-2-5 9:15:00'
union (bid_id,seller_id,buyer_id,bid_time) select 52368, 2590, 1598, '2003-2-6 9:20:00'
union (bid_id,seller_id,buyer_id,bid_time) select 64427, 3176, 2436, '2003-2-7 13:40:00'
update #bids
set seller_class=b.class_new,buyer_class=c.class_new
from #bids a ,
( select user_id,class_new from #userclassinfo
where changer_class_time=(select max(changer_class_time) from #userclassinfo a where a.user_id=#userclassinfo.user_id ))b,
( select user_id,class_new from #userclassinfo
where changer_class_time=(select max(changer_class_time) from #userclassinfo a where a.user_id=#userclassinfo.user_id ))c
where a.seller_id=b.user_id and a.buyer_id=c.user_id
-------------
bid_id seller_id buyer_id bid_time datetime seller_class buyer_class
52367 3176 1598 2003-02-05 09:15:00.000 3 2
52368 2590 1598 2003-02-06 09:20:00.000 1 2
64427 3176 2436 2003-02-07 13:40:00.000 3 2