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
(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
解决方案 »
- 一个怪异的SQL查询,大侠们进来给看看.着急.
- 求sql语句
- 一个字符型字段[是一个编码]设置了外键约束,可是能否让这个字段还能存空白字符串呀?
- insert 语句报错,求解决呀...
- 关于sqlserver2005图片存储的问题
- 关于一个SQL Server的SELECT问题
- 关于SQL的极简单的问题,请各位老师,大侠,高手进入!
- 关于触发器
- 为什么我的捕抓不到错误?
- 判断数据库中是否存在某个表的通用方法是什么?
- 如何找到局域网内其他机子上的SQLServer?
- 请问:SELECT * FROM Table1 WHERE ID NOT IN (SELECT ID FROM Table2)的SQL如何改写成用JOIN的语句?
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
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 tbupdate 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
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'