这个如何删除呢？

[wangxiao2008]

想留下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

[SQL2088]

--> 测试数据: [t1]
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',3

delete 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 行受影响)

[「已注销」]

SELECT distinct t.* FROM #t1 t
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.no

no         id
-------------------- --
eee        1
bbb        2
kkk        3

(3 row(s) affected)