表a
htbh cpbh bh blq
11 001 0001 2006-1-12
11 001 0002 2006-1-13
表b
htbh cpbh blq
11 001
如何把表A最晚的日期更新到B表中
使表B的数据变为
htbh cpbh blq
11 001 2006-1-13
条件a.htbh=b.htbh and a.cpbh=b.cpbh
htbh cpbh bh blq
11 001 0001 2006-1-12
11 001 0002 2006-1-13
表b
htbh cpbh blq
11 001
如何把表A最晚的日期更新到B表中
使表B的数据变为
htbh cpbh blq
11 001 2006-1-13
条件a.htbh=b.htbh and a.cpbh=b.cpbh
from B
inner join (select htbh,cpbh,max(blq) as dt from A group by htbh,cpbh) t
on B.htbh=t.htbh and B.cpbh=t.cpbh
go
set nocount on
go
if exists(select * from sysobjects where type = 'U' and name = 'a')
drop table a
go
create table a
(htbh int not null,
cpbh int not null,
bh int not null,
blq datetime)
go
insert into a
values(11,001,0001,'2006-1-12')
insert into a
values(11,001,0002,'2006-1-13')
go
if exists(select * from sysobjects where type = 'U' and name = 'b')
drop table b
go
create table b
(htbh int not null,
cpbh int not null,
blq datetime null)
go
--insert into b(htbh,cpbh)
--values(11,001)
--go
if exists(select * from sysobjects where type = 'TR' and name = 'triInsert')
drop table triInsert
go
create trigger triInsert
on a
for insert
as
begin
declare @newHtbh int, @newCpbh int, @sj datetime
select @newHtbh = htbh from inserted
select @newCpbh = cpbh from inserted
--select @newHtbh
--select @newCpbh
select @sj = max(blq) from a
--select @sj
if exists(select * from b where htbh = @newHtbh and cpbh = @newCpbh)
begin
update b
set blq = @sj
where htbh = @newHtbh and cpbh = @newCpbh
end
else
begin
insert into b
values(@newHtbh,@newCpbh,@sj)
end
end
go
select * from b
go
insert into a
values(11,001,0003,'2006-1-14')
select * from b
insert into a
values(11,001,0003,'2006-1-1')
select * from b
insert into a
values(11,001,0003,'2006-2-18')
select * from b