想留下t1里面id相同的记录只有一条,条件是关联t2,id相同留下t2中日期小的那个
t1
no id
aaa 1
bbb 2
ccc 1
ddd 2
eee 1
kkk 3
t2
date no id
20081009 aaa 1
20030201 bbb 2
20040306 ccc 1
20050102 ddd 2
20030608 eee 1
20060207 kkk 3 结果应该是t1:
no id
eee 1
bbb 2
kkk 3
t1
no id
aaa 1
bbb 2
ccc 1
ddd 2
eee 1
kkk 3
t2
date no id
20081009 aaa 1
20030201 bbb 2
20040306 ccc 1
20050102 ddd 2
20030608 eee 1
20060207 kkk 3 结果应该是t1:
no id
eee 1
bbb 2
kkk 3
if object_id('[t1]') is not null drop table [t1]
go
create table [t1] (no varchar(3),id int)
insert into [t1]
select 'aaa',1 union all
select 'bbb',2 union all
select 'ccc',1 union all
select 'ddd',2 union all
select 'eee',1 union all
select 'kkk',3
--> 测试数据: [t2]
if object_id('[t2]') is not null drop table [t2]
go
create table [t2] (date datetime,no varchar(3),id int)
insert into [t2]
select '20081009','aaa',1 union all
select '20030201','bbb',2 union all
select '20040306','ccc',1 union all
select '20050102','ddd',2 union all
select '20030608','eee',1 union all
select '20060207','kkk',3delete a
from t1 a,t2 b
where a.id=b.id and a.no=b.no
and exists(select 1 from t2 where id=b.id and date<b.date)select * from [t1]
no id
---- -----------
bbb 2
eee 1
kkk 3(3 行受影响)
INNER JOIN
(
SELECT * FROM #t2 t WHERE NOT EXISTS(SELECT NULL FROM #t2 WHERE t.id=id AND t.date>date)
)M
ON t.id=m.id AND t.no=m.nono id
-------------------- --
eee 1
bbb 2
kkk 3(3 row(s) affected)