日期:2014-05-17 浏览次数:20526 次
create table A(fcustid int, fentryid int, fitemid int, fprice numeric(12,2))
create table B(fdate datetime, fcustid int, fitemid int, fprice numeric(12,2))
insert into A
select 123,1,1111,0
union all select 123,2,2222,0
union all select 123,3,2222,0
union all select 123,4,3333,99 -->已经改为99,结果一样的
union all select 123,5,4444,0
insert into B
select '2013/1/1',123,1111,10
union all select '2013/3/1',123,2222,15
union all select '2013/3/2',123,2222,16
union all select '2013/3/2',123,2222,17
union all select '2013/3/2',123,4444,22
union all select '2013/3/12',123,1111,11
union all select '2013/3/22',123,4444,24
update a set fprice=case when isnull(b.fprice,0)=0 then 0.1 else b.fprice end
from a
left join
(select b.*
from b
inner join (select fcustid,fitemid,max(fdate) as fdate from b group by fcustid,fitemid)t