我有二个表:
A1
id name noid update
1 aa A001 2008-01-01
2 bb B002 2008-03-05
3 cc C003 2008-03-10
4 ee E004 2008-07-12
.....
A2
id name update
1 aa 2008-02-01
2 aa 2008-04-01
3 bb 2008-04-01
4 ee 2008-06-20
....A1中name是不重复的,
A2中的name有重复的,并且可能在A1有,A2不一定有我想A1中update显示A1,A2中最新的日期,id name noid update
1 aa A001 2008-04-01
2 bb B002 2008-04-01
3 cc C003 2008-03-10
4 ee E004 2008-07-12
.....
A1
id name noid update
1 aa A001 2008-01-01
2 bb B002 2008-03-05
3 cc C003 2008-03-10
4 ee E004 2008-07-12
.....
A2
id name update
1 aa 2008-02-01
2 aa 2008-04-01
3 bb 2008-04-01
4 ee 2008-06-20
....A1中name是不重复的,
A2中的name有重复的,并且可能在A1有,A2不一定有我想A1中update显示A1,A2中最新的日期,id name noid update
1 aa A001 2008-04-01
2 bb B002 2008-04-01
3 cc C003 2008-03-10
4 ee E004 2008-07-12
.....
select a.id , a1.name ,a1.noid,
[update] = (select top 1 update from a2 where a2.name = a1.name order by [update] desc)
from a1--update
update a1
set [update] = (select top 1 update from a2 where a2.name = a1.name order by [update] desc)
from a1
declare @A1 table(id int,name varchar(10),noid varchar(10),[update] datetime)
insert into @A1 select 1,'aa','A001','2008-01-01'
insert into @A1 select 2,'bb','B002','2008-03-05'
insert into @A1 select 3,'cc','C003','2008-03-10'
insert into @A1 select 4,'ee','E004','2008-07-12' declare @A2 table(id int,name varchar(10),[update] datetime)
insert into @A2 select 1,'aa','2008-02-01'
insert into @A2 select 2,'aa','2008-04-01'
insert into @A2 select 3,'bb','2008-04-01'
insert into @A2 select 4,'ee','2008-06-20'
select t.id,t.name,t.noid,isnull((select max([update]) from @A2 where name=t.name),t.[update]) as [update] from @A1 t/*
id name noid update
----------- ---------- ---------- ------------------------------------------------------
1 aa A001 2008-04-01 00:00:00.000
2 bb B002 2008-04-01 00:00:00.000
3 cc C003 2008-03-10 00:00:00.000
4 ee E004 2008-06-20 00:00:00.000
*/
from a
left join (
select b.*
from b inner join(select name,max(update) as update from b group by name)c
on b.name=c.name and b.update=c.update)t
on a.id=t.id
insert into @A1 select 1,'aa','A001','2008-01-01'
insert into @A1 select 2,'bb','B002','2008-03-05'
insert into @A1 select 3,'cc','C003','2008-03-10'
insert into @A1 select 4,'ee','E004','2008-07-12' declare @A2 table(id int,name varchar(10),[update] datetime)
insert into @A2 select 1,'aa','2008-02-01'
insert into @A2 select 2,'aa','2008-04-01'
insert into @A2 select 3,'bb','2008-04-01'
insert into @A2 select 4,'ee','2008-06-20'
select
t.id,t.name,t.noid,
isnull((select max(a.[update]) from (select name,[update] from @A1 union select name,[update] from @A2) a where a.name=t.name),t.[update]) as [update]
from @A1 t/*
id name noid update
----------- ---------- ---------- ------------------------------------------------------
1 aa A001 2008-04-01 00:00:00.000
2 bb B002 2008-04-01 00:00:00.000
3 cc C003 2008-03-10 00:00:00.000
4 ee E004 2008-07-12 00:00:00.000
*/
insert into a1 values(1 , 'aa' , 'A001', '2008-01-01')
insert into a1 values(2 , 'bb' , 'B002', '2008-03-05')
insert into a1 values(3 , 'cc' , 'C003', '2008-03-10')
insert into a1 values(4 , 'ee' , 'E004', '2008-07-12')
create table A2(id int, name varchar(10),[update] datetime)
insert into a2 values(1 , 'aa' , '2008-02-01')
insert into a2 values(2 , 'aa' , '2008-04-01')
insert into a2 values(3 , 'bb' , '2008-04-01')
insert into a2 values(4 , 'ee' , '2008-06-20')--search
select a1.id , a1.name ,a1.noid,
[update] = isnull((select top 1 [update] from a2 where a2.name = a1.name order by [update] desc),a1.[update])
from a1
/*
id name noid update
----------- ---------- ---------- ------------------------------------------------------
1 aa A001 2008-04-01 00:00:00.000
2 bb B002 2008-04-01 00:00:00.000
3 cc C003 2008-03-10 00:00:00.000
4 ee E004 2008-06-20 00:00:00.000(所影响的行数为 4 行)
*/--update
update a1
set [update] = isnull((select top 1 [update] from a2 where a2.name = a1.name order by [update] desc),a1.[update])
from a1
select * from a1
/*
id name noid update
----------- ---------- ---------- ------------------------------------------------------
1 aa A001 2008-04-01 00:00:00.000
2 bb B002 2008-04-01 00:00:00.000
3 cc C003 2008-03-10 00:00:00.000
4 ee E004 2008-06-20 00:00:00.000(所影响的行数为 4 行)
*/drop table a1, a2
declare @A1 table(id int,name varchar(10),noid varchar(10),[update] datetime)
insert into @A1 select 1,'aa','A001','2008-01-01'
insert into @A1 select 2,'bb','B002','2008-03-05'
insert into @A1 select 3,'cc','C003','2008-03-10'
insert into @A1 select 4,'ee','E004','2008-07-12' declare @A2 table(id int,name varchar(10),[update] datetime)
insert into @A2 select 1,'aa','2008-02-01'
insert into @A2 select 2,'aa','2008-04-01'
insert into @A2 select 3,'bb','2008-04-01'
insert into @A2 select 4,'ee','2008-06-20'
select
a.[ID], a.[Name], a.noid,
case when b.[update] is null then a.[update] else b.[update] end as [update]
from @A1 a
left join (select [name], max([update]) as [update] from @A2 group by [name]) as b
on a.[Name]=b.[Name]
select t.id,t.name,t.noid,isnull((select max([update]) from A2 where name=t.name and [update]>t.[update]),t.[update]) as [update] from A1 t加个判断条件就可以了吧