update b set b.time = a.time
from B表 b
join (select bookcode,max(time) as time from a表 group by bookcode) a
on b.bookcode = a.bookcode and b.time <a.time
from B表 b
join (select bookcode,max(time) as time from a表 group by bookcode) a
on b.bookcode = a.bookcode and b.time <a.time
from B表 as a ,
(select bookcode,max(time) as time from a表 group by bookcode) as b
where a.bookcode = b.bookcode and a.time <= b.time
update b set [time]=a.[time]
from b join(
select bookcode,[time]=max(a.[time]) from a
group by bookcode
) a on a.bookcode=b.bookcode
where a.[time]>b.[time]--添加不存在的记录
insert into b select a.bookcode,max(a.[time])
from a join b on a.bookcode=b.bookcode
where b.bookcode is null
group by a.bookcode
from b join(
select * from a a1 where [time]=(select max(a.[time]) from a where bookcode=a1.bookcode)
) a on a.bookcode=b.bookcode
where a.[time]>b.[time]--添加不存在的记录
insert into b select * from(
select * from a a1 where [time]=(select max(a.[time]) from a where bookcode=a1.bookcode)
)a join b on a.bookcode=b.bookcode
where b.bookcode is null
group by a.bookcode
--建议删除已经存在的,全新重新增--删除已经存在的
delete b
from b join(
select bookcode,[time]=max([time]) from a group by bookcode
) a on a.bookcode=b.bookcode
where a.[time]>b.[time]--添加不存在的记录
insert into b select * from(
select * from a a1 where [time]=(select max(a.[time]) from a where bookcode=a1.bookcode)
)a join b on a.bookcode=b.bookcode
where b.bookcode is null
group by a.bookcode
create trigger ta_iu_a
on a
for insert ,update
as
if exists(select 1 from inserted as inse left join b on inse.bookcode=b.bookcode)
begin
if exit(select 1 from inserted as inse left join b on inse.bookcode=b.bookcode where b.time<inse.time)
update b set time=(select time from inserted as inse where inse.bookcode=b.bookcode)
end
else --没有记录插入记录
begin
insert into b values(inserted 表中的值)
end
go
set b.time = v.time
from (select distinct time,bookcode
from a
where a.time =(select max(time) from a as c where a.bookcode = c.bookcode)
) as v
where v.bookcode = b.bookcode
select distinct time,bookcode
from a
where not exists(select 1 from b
where a.bookcode = b.bookcode
and a.time <= b.time)
and a.time = (select max(time) from a as c where a.bookcode = c.bookcode)