复杂的取值不知怎么弄,请能帮我看看sql语名怎么写?
表1的FPRICE要取值
如果表1.fcustid 与fitemid在表2中是否存在,如果存在就用表2.fprice更新表1的FPRICE,如果表2存在多行,就取日期最大的那一行的值,如果在表2中不存在,表1.FPRICE=0.1
请能帮我看看sql语名怎么写
表1
fcustidfentryid fitemid fprice
1231 1111 0
1232 2222 0
1233 2222 0
1234 3333 0
1235 4444 0
表2
fdate fcustidfitemidfprice
2013/1/1123111110
2013/3/1123222215
2013/3/2123222216
2013/3/2123222217
2013/3/2123444422
2013/3/12123111111
2013/3/22123444424
实现表1
fcustidfentryid fitemid fprice
1231111111
1232222217
1233222217
123433330.1
1235444424
[解决办法]
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
on t.fcustid=b.fcustid and t.fitemid=b.fitemid and b.fdate=t.fdate
)b
on a.fcustid=b.fcustid and a.fitemid=b.fitemid
select * from A
drop table A,B
/*
fcustid fentryid fitemid fprice
-----------------------------------------
1231111111.00
1232222217.00
1233222217.00
12343333.10
1235444424.00
*/