delete from Table t where ( t.a ,t.b ,t.c ) in (select a,b,c from Table group by a,b,c having count(*) > 1) and id not in (select min(id) from Table group by a,b,c having count(*)>1) 我想删除Table表中a,b,c字段都相同的条数(只保留最小id)
delete t from [Table] t where exists( select 1 from [Table] where t.a=a and t.b=b and t.c=c and t.id>id)
--2005 ;WITH Liang AS ( SELECT rowid=ROW_NUMBER() OVER(PARTITION BY a,b,c ORDER BY id),* FROM [Table] ) DELETE Liang WHERE rowid > 1;--2000DELETE FROM A FROM [Table] AS A JOIN (SELECT a,b,c,MIN(id) AS id FROM [Table] GROUP BY a,b,c HAVING COUNT(*) > 1) AS B ON A.a = B.a AND A.b = B.b AND A.c = B.c AND A.id > B.id;DELETE FROM A FROM [Table] AS A WHERE EXISTS(SELECT * FROM [Table] WHERE a = A.a AND b = A.b AND c = A.c AND id < A.id);
delete a from tb a where ID not in (select min(ID) from tb where a.a=a and a.b=b and a.c=c and a.id>id)
delete T from Table1 t where EXISTS(SELECT 1 FROM TABLE1 WHERE A=T.A AND B=T.B AND C=T.C AND ID<T.ID)
delete t where id in (select id from Table group by a,b,c having count(*) > 1)
delete from Table t
where not exists(select 1 from Table where t.a=a and t.b=b and t.c=c and t.id>id)
where exists(
select 1 from [Table]
where t.a=a and t.b=b and t.c=c and t.id>id)
;WITH Liang AS
(
SELECT rowid=ROW_NUMBER() OVER(PARTITION BY a,b,c ORDER BY id),*
FROM [Table]
)
DELETE Liang WHERE rowid > 1;--2000DELETE FROM A
FROM [Table] AS A
JOIN (SELECT a,b,c,MIN(id) AS id FROM [Table]
GROUP BY a,b,c HAVING COUNT(*) > 1) AS B
ON A.a = B.a AND A.b = B.b AND A.c = B.c AND A.id > B.id;DELETE FROM A
FROM [Table] AS A
WHERE EXISTS(SELECT * FROM [Table]
WHERE a = A.a AND b = A.b AND c = A.c
AND id < A.id);
where EXISTS(SELECT 1 FROM TABLE1 WHERE A=T.A AND B=T.B AND C=T.C AND ID<T.ID)