请问这个Update 语句该怎么写？

[zp2002]

有一个表，如下：

F1 F2 V1 V2
200305 M1 0 800
200305 M2 0 300
200306 M1 0 100
200306 M2 0 200
200306 M3 0 300
200307 M4 0 400
200307 M5 0 500
200307 M6 0 600
200307 M7 0 100
200307 M8 0 100

200308 M1 0 100
200308 M2 0 100
200308 M3 0 100
200308 M4 0 100
200308 M5 0 100

现在要将 F1 = 200308 中的 V1 值，用其他的 F1 <200308，但是最大F1 值的 V2代替，执行结果如下：
200308 M1 100 100
200308 M2 200 100
200308 M3 300 100
200308 M4 400 100
200308 M5 500 100

请问SQL该如何写？

[Andy__Huang]

update tb set v1=c.v2 from
(select a.* from (select max(F1) as F1,F2 from tb where F1<'200308' group by F1,F2)a
inner join
(select * from tb where F1<'200308')b
on a.F1=b.F1 and a.F2=b.F2)c
where tb.F1=c.F1 and tb.F2=c.F2 and tb.F1=200308

[zjcxc]

update a set V1=b.V2
from 表 a,表 b,(
select F1=max(F1),F2
from 表
where F1<200308
group by F2
)c where a.F1=200308
and a.F2=c.F2
and b.F1=c.F1 and b.F2=c.F2

[Andy__Huang]

改一下sql語句

create table tb(F1 varchar(6),F2 varchar(10),V1 numeric(10), V2 numeric(10))
Insert into tb
select '200305','M1','0','800'
union all select '200305','M2','0','300'
union all select '200306','M1','0','100'
union all select '200306','M2','0','200'
union all select '200306','M3','0','300'
union all select '200307','M4','0','400'
union all select '200307','M5','0','500'
union all select '200307','M6','0','600'
union all select '200307','M7','0','100'
union all select '200307','M8','0','100'
union all select '200308','M1','0','100'
union all select '200308','M2','0','100'
union all select '200308','M3','0','100'
union all select '200308','M4','0','100'
union all select '200308','M5','0','100'

select * from tb


create table tb(F1 varchar(6),F2 varchar(10),V1 numeric(10), V2 numeric(10))
Insert into tb
select '200305','M1','0','800'
union all select '200305','M2','0','300'
union all select '200306','M1','0','100'
union all select '200306','M2','0','200'
union all select '200306','M3','0','300'
union all select '200307','M4','0','400'
union all select '200307','M5','0','500'
union all select '200307','M6','0','600'
union all select '200307','M7','0','100'
union all select '200307','M8','0','100'
union all select '200308','M1','0','100'
union all select '200308','M2','0','100'
union all select '200308','M3','0','100'
union all select '200308','M4','0','100'
union all select '200308','M5','0','100'

select * from tb

update tb set v1=c.v2 from
(select b.* from (select distinct max(F1) as F1,F2 from tb where F1<'200308' group by F2)a
inner join
(select * from tb where F1<'200308')b
on a.F1=b.F1 and a.F2=b.F2)c
where tb.F1='200308' and tb.F2=c.F2

--結果
select * from tb where F1='200308'
F1 F2 V1 V2
-------------------------------------
200308 M1 100 100
200308 M2 200 100
200308 M3 300 100
200308 M4 400 100
200308 M5 500 100

[Andy__Huang]

暈了﹐上面多copy了一段代碼

[zp2002]

呵﹐謝謝各位﹐但不知有沒有更快的方法啊﹖如通過自聯接之類﹖

[yujohny]

楼主试试我下面这条语句
UPDATE A
SET A.V1=(SELECT TOP 1 V2 FROM 表 WHERE F1<A.F1 AND F2=A.F2 ORDER BY F1 DESC)
FROM 表 A
WHERE A.F1='200308'

[yjbnew]

up