delete a from a join b on a.site_id=b.site_idinsert a (site_id,mvalue,dattim)
select b.site_id,b.mvalue,b.dattim
from b join ( select site_id,max(dattim) as mt
from b
group by site_id
) c
on b.site_id=c.site_id and b.dattim=c.mt
select b.site_id,b.mvalue,b.dattim
from b join ( select site_id,max(dattim) as mt
from b
group by site_id
) c
on b.site_id=c.site_id and b.dattim=c.mt
declare @tb1 table (site_id int,mvalue decimal(28,2),dattim datetime)
insert into @tb1
select 1, 2.00, '2004-03-06 12:56:48' union all
select 2, 4.00, '2004-05-08 13:23:56'
declare @tb2 table (site_id int,mvalue decimal(28,2),dattim datetime)
insert into @tb2
select 1, 2.00, '2004-03-06 12:56:48' union all
select 2, 4.00, '2004-05-08 13:23:56' union all
select 1, 3.00, '2004-08-09 12:12:36' union all
select 3, 5.00, '2004-02-02 02:21:36'
/*
选择B表中相同的子站ID的dattim最大的记录的相应的字段，如果这个子站ID在A表中没有，那么把这个记录插入，如果有则修改相应的直。
执行后应该是
A 表中一些的字段(后来的直)
SITE_ID mvalue dattim
1 3.00 2004-08-09 12:12:36
2 4.00 2004-05-08 13:23:56
3 5.00 2004-02-02 02:21:36
*/
select site_id_#1=b.site_id,mvalue_#1=b.mvalue,dattim_#1=b.dattim into #1 from @tb2 b right join (select site_id,dattim=max(dattim) from @tb2 group by site_id)c on b.site_id=c.site_id and b.dattim=c.dattim
select * from #1
insert into @tb1 select site_id_#1,mvalue_#1,dattim_#1 from #1 where site_id_#1 not in (select site_id from @tb1)
update @tb1 set mvalue=#1.mvalue_#1,dattim=#1.dattim_#1 from #1 where site_id=#1.site_id_#1
select * from @tb1
drop table #1/*（所影响的行数为 2 行）
（所影响的行数为 4 行）
（所影响的行数为 3 行）site_id_#1 mvalue_#1 dattim_#1
----------- ------------------------------ ------------------------------------------------------
1 3.00 2004-08-09 12:12:36.000
2 4.00 2004-05-08 13:23:56.000
3 5.00 2004-02-02 02:21:36.000（所影响的行数为 3 行）
（所影响的行数为 1 行）
（所影响的行数为 3 行）site_id mvalue dattim
----------- ------------------------------ ------------------------------------------------------
1 3.00 2004-08-09 12:12:36.000
2 4.00 2004-05-08 13:23:56.000
3 5.00 2004-02-02 02:21:36.000（所影响的行数为 3 行）
*/