34,593
社区成员
发帖
与我相关
我的任务
分享
create table t_1(ID int, MID int, Date datetime)
insert t_1 select 1, NULL, '2007-1-21'
insert t_1 select 2, NULL, '2007-3-25'
insert t_1 select 3, NULL, '2007-3-26'
create table t_2(ID int, Date datetime)
insert t_2 select 1, '2007-1-22'
insert t_2 select 2, '2007-1-25'
insert t_2 select 3, '2007-1-29'
--select a.*,b.*
update a set mid=b.id
from t_1 a
left join t_2 b
on b.date=
(select max(date) from t_2 x
where date<=a.date
and not exists(select 1 from t_1 y
where id<a.id and date>=x.date
and not exists(select 1 from t_2 where date>x.date and date<=y.date)
)
)
select * from t_1
drop table t_1,t_2
create table t_1(ID int, MID int, Date datetime)
insert t_1 select 1, NULL, '2007-1-21'
insert t_1 select 2, NULL, '2007-3-25'
insert t_1 select 3, NULL, '2007-3-26'
create table t_2(ID int, Date datetime)
insert t_2 select 1, '2007-1-22'
insert t_2 select 2, '2007-1-25'
insert t_2 select 3, '2007-1-29'
go
declare test cursor for
select ID,[Date] from t_1
declare @ID int,@Date datetime
open test
fetch next from test into @ID,@Date
while @@fetch_status=0
begin
update t_1
set MID=(select ID from t_2 where Date=(select max(Date) from T_2 where Date<t_1.Date and ID not in(select isnull(MID,'') from t_1)))
where
ID=@ID
fetch next from test into @ID,@Date
end
close test
deallocate test
--drop table dbo.t_2
select * from t_1
ID MID Date
----------- ----------- ------------------------------------------------------
1 NULL 2007-01-21 00:00:00.000
2 3 2007-03-25 00:00:00.000
3 2 2007-03-26 00:00:00.000
(所影响的行数为 3 行)