两个表
T1 c1 c2 c3
a1 b1 2009-05-27
a2 b2 2009-05-29
a1 b1 2009-05-28
a1 b1 2009-05-26
....
T2 c4 c5 c6
a1 b5 2009-05-28
a2 b6 2009-05-28
....问1:删除T1中,当c1=c4(T2表),且c3>c6(T2表)的数据
问2:在T1表中,当c1、c2列相同时,保留c3最大的那一列
T1 c1 c2 c3
a1 b1 2009-05-27
a2 b2 2009-05-29
a1 b1 2009-05-28
a1 b1 2009-05-26
....
T2 c4 c5 c6
a1 b5 2009-05-28
a2 b6 2009-05-28
....问1:删除T1中,当c1=c4(T2表),且c3>c6(T2表)的数据
问2:在T1表中,当c1、c2列相同时,保留c3最大的那一列
FROM t1 AS A
JOIN t2 AS B
ON A.c1=B.c4 AND A.c3>B.c6---
SELECT *
FROM t1 AS A
WHERE NOT EXISTS(SELECT * FROM t1 WHERE c1=A.c1 AND c2=A.c2 AND c3>A.c3)
from T2
where t1.c1=t2.c4 and t1.c3>t2.c6select c1,c2,max(c3) as c3 from t1 group by c1,c2
delete a from t1 a join t2 b on a.c1=b.c4 and a.c3>b.c6
delete t
from t1 t
where not exists(select * from t1 where c1=t.c1 and c2=t.c2 and c3>t.c3)
2:
delete from T1 where c3 not in (select c3 from T1 as D where D.c1=D.c2 And c3<D.c3)
DELETE T1
from T2
where t1.c1=t2.c4 and t1.c3>t2.c6
第二题
select c1,c2,max(c3) as c3 from t1 group by c1,c2
go
create table [t1]([c1] varchar(10),[c2] varchar(10),[c3] varchar(10))
insert [t1] select 'a1','b1','2009-05-27'
union all select 'a2','b2','2009-05-29'
union all select 'a1','b1','2009-05-28'
union all select 'a1','b1','2009-05-26'
go
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([c4] varchar(10),[c5] varchar(10),[c6] varchar(10))
insert [t2] select 'a1','b5','2009-05-28'
union all select 'a2','b6','2009-05-28'
go
--1.
delete a from t1 a join t2 b on a.c1=b.c4 and a.c3>b.c6select * from t1
/*
c1 c2 c3
---------- ---------- ----------
a1 b1 2009-05-27
a1 b1 2009-05-28
a1 b1 2009-05-26(3 行受影响)
*/
--2.
delete t
from t1 t
where exists(select * from t1 where c1=t.c1 and c2=t.c2 and c3>t.c3)select * from t1
/*
c1 c2 c3
---------- ---------- ----------
a2 b2 2009-05-29
a1 b1 2009-05-28(2 行受影响)
*/MODIFY
delete t
from t1 t
where not exists(select * from t1 where c1=t.c1 and c2=t.c2 and c1=c2 and c3>t.c3)
delete t1 from t2 where c1=c4 and c3>c6
2、
delete a from t1 a where c3 <> (select max(c3) from t1 where c1=a.c1 and c2=a.c2)
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([c1] varchar(10),[c2] varchar(10),[c3] varchar(10))
insert [t1] select 'a1','b1','2009-05-27'
union all select 'a2','b2','2009-05-29'
union all select 'a1','b1','2009-05-28'
union all select 'a1','b1','2009-05-26'
go
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([c4] varchar(10),[c5] varchar(10),[c6] varchar(10))
insert [t2] select 'a1','b5','2009-05-28'
union all select 'a2','b6','2009-05-28'
go--1、
delete t1 from t2 where c1=c4 and c3>c6
select * from t1
/*
c1 c2 c3
---------- ---------- ----------
a1 b1 2009-05-27
a1 b1 2009-05-28
a1 b1 2009-05-26(所影响的行数为 3 行)
*/
2、
delete a from t1 a where c3 <> (select max(c3) from t1 where c1=a.c1 and c2=a.c2)
/*
c1 c2 c3
---------- ---------- ----------
a2 b2 2009-05-29
a1 b1 2009-05-28(所影响的行数为 2 行)
*/