我有两个表
a表和B表a表
id num1 date1
1 5 2009-08-06
2 6 2009-08-07
2 1 2009-08-05
2 4 2009-08-11
2 6 2009-08-08
2 8 2009-08-20b表id sum1 date1
2 0 2009-08-10
1 0 2009-08-16
我要把 两表中ID相同且date比表小 的最近的记录的 num1更新到sum1中
a表和B表a表
id num1 date1
1 5 2009-08-06
2 6 2009-08-07
2 1 2009-08-05
2 4 2009-08-11
2 6 2009-08-08
2 8 2009-08-20b表id sum1 date1
2 0 2009-08-10
1 0 2009-08-16
我要把 两表中ID相同且date比表小 的最近的记录的 num1更新到sum1中
go
create table A
(
id int,
num1 int,
date1 datetime
)
insert into A select 1,5,'2009-08-06'
union all select 2,6,'2009-08-07'
union all select 2,1,'2009-08-05'
union all select 2,4,'2009-08-11'
union all select 2,6,'2009-08-08'
union all select 2,8,'2009-08-20'
goif object_id('B') is not null drop table B
go
create table B
(
id int,
sum1 int,
date1 datetime
)
go
insert into B select 2,0,'2009-08-10'
insert into B select 1,0,'2009-08-16'update B1
set b1.sum1=A1.num1
from
(
select * from A A1 where not exists(select * from A where A1.id=id and A1.num1<num1)
)
A1,B B1
where A1.id=B1.idselect * from Bid sum1 date1
----------- ----------- -----------------------
2 8 2009-08-10 00:00:00.000
1 5 2009-08-16 00:00:00.000(2 行受影响)
就是时间小,相差最小
ws_hgo好像解决了
验证中..
呵呵
好像有问题
是date1小于等于
再说是我改了下也不对
select a.id,min(datediff(day,a.date1,b.date1)) as mindate from a left join b on a.id=b.id group by a.id ;update b set sum1=num1 from a inner join c on a.id=c.id inner join b on a.id=c.id
try..use test
go
if object_id('test.dbo.a') is not null drop table a
-- 创建数据表
create table a
(
id int,
num1 int,
date1 datetime
)
go
--插入测试数据
insert into a select 1,5,'2009-08-06'
union all select 2,6,'2009-08-07'
union all select 2,1,'2009-08-05'
union all select 2,4,'2009-08-11'
union all select 2,6,'2009-08-08'
union all select 2,8,'2009-08-20'
go
if object_id('test.dbo.b') is not null drop table b
-- 创建数据表
create table b
(
id int,
sum1 int,
date1 datetime
)
go
--插入测试数据
insert into b select 2,0,'2009-08-10'
union all select 1,0,'2009-08-16'
go
--代码实现;with t as(select a.num1,b.*,diff=abs(datediff(dy,a.date1,b.date1)) from a join b on a.id=b.id)
update b set sum1=tt.num1
from b,t tt
where b.id=tt.id and b.date1=tt.date1
and not exists(select 1 from t where id=tt.id and diff<tt.diff)select * from b/*测试结果id sum1 date1
---------------------------------------
2 4 2009-08-10 00:00:00.000
1 5 2009-08-16 00:00:00.000(2 行受影响)
*/
update b set sum1=tt.num1
from b,(select a.num1,b.*,diff=abs(datediff(dy,a.date1,b.date1))
from a join b
on a.id=b.id)tt
where b.id=tt.id and b.date1=tt.date1
and not exists(select 1
from (
select a.num1,b.*,diff=abs(datediff(dy,a.date1,b.date1))
from a join b on a.id=b.id)t
where id=tt.id and diff<tt.diff)
是date1小于等于??莫非是这样:
'楼主啊!发帖的时候最好把想要的最终结果“数据集”也贴出来!不然大家就要猜测你的需求了'
update b set sum1=tt.num1
from b,(select a.num1,b.*,diff=abs(datediff(dy,a.date1,b.date1))
from a join b
on a.id=b.id and a.date1<=b.date1)tt
where b.id=tt.id and b.date1=tt.date1
and not exists(select 1
from (select a.num1,b.*,diff=abs(datediff(dy,a.date1,b.date1))
from a join b
on a.id=b.id and a.date1<=b.date1)t
where id=tt.id and diff<tt.diff)select * from b/*测试结果id sum1 date1
---------------------------------------
2 6 2009-08-10 00:00:00.000
1 5 2009-08-16 00:00:00.000(2 行受影响)
*/
3Q
下次改进
(
id int,
num1 int,
date1 datetime
)
insert into #t1
select 1,5,'2009-08-06' union all
select 2,6,'2009-08-07' union all
select 2,1,'2009-08-05' union all
select 2,4,'2009-08-11' union all
select 2,6,'2009-08-08' union all
select 2,8,'2009-08-20' create table #t2
(
id int,
sum1 int,
date1 datetime
)
insert into #t2
select 2,0,'2009-08-10' union all
select 1,0,'2009-08-16'update #t2 set #t2.sum1=b.num1
from (
select a.id,b.num1,a.date1 from (select id,max(date1) date1 from #t1 group by id) a
left join #t1 b on a.id=b.id and a.date1=b.date1
) b
where #t2.id=b.id and b.date1>#t2.date1select * from #t2