delete tb where exists(select 1 from tb t where t.mc=tb.mc and t.id<tb.id)
if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[mc] varchar(2)) insert [tb] select 1,'aa' union all select 2,'bb' union all select 3,'aa' union all select 4,'cc' godelete tb where exists(select 1 from tb t where t.mc=tb.mc and t.id<tb.id) goselect * from tb /** id mc ----------- ---- 1 aa 2 bb 4 cc **/
delete a from tb a where ID not in (select min(ID) from tb where mc=a.mc)
--> 测试数据:#t if object_id('tempdb.dbo.#t') is not null drop table #t go create table #t([id] int,[mc] varchar(2)) insert #t select 1,'aa' union all select 2,'bb' union all select 3,'aa' union all select 4,'cc'--方法1: delete a from #t a where exists(select 1 from #t where [mc]=a.[mc] and [id]<a.[id])--方法2: delete a from #t a left join (select min([id])[id],[mc] from #t group by [mc]) b on a.[mc]=b.[mc] and a.[id]=b.[id] where b.[id] is null--方法3: delete a from #t a where [id] not in (select min([id]) from #t where [mc]=a.[mc])--方法4(注:[id]为唯一时可用): delete a from #t a where [id] not in(select min([id])from #t group by [mc])--方法5: delete a from #t a where (select count(1) from #t where [mc]=a.[mc] and [id]<a.[id])>0方法6: delete a from #t a where [id]<>(select top 1 [id] from #t where [mc]=a.[mc] order by [id])方法7: delete a from #t a where [id]>any(select [id] from #t where [mc]=a.[mc])
--瞎写一个 DECLARE @tab TABLE(id INT,mc VARCHAR(10))INSERT INTO @tab(id,mc) SELECT 1,'aa' UNION ALL SELECT 2,'bb' UNION ALL SELECT 3,'aa' UNION ALL SELECT 4,'cc' DELETE FROM @tab WHERE id not IN (SELECT MIN(t.id) FROM @tab AS t GROUP BY t.mc HAVING COUNT(*) > 1) AND id IN (SELECT t1.id FROM @tab AS t1 INNER JOIN @tab AS t2 ON t1.id <> t2.id AND t1.mc = t2.mc)SELECT * FROM @tab /* id mc ----------- ---------- 1 aa 2 bb 4 cc */
go
create table [tb]([id] int,[mc] varchar(2))
insert [tb]
select 1,'aa' union all
select 2,'bb' union all
select 3,'aa' union all
select 4,'cc'
godelete tb where exists(select 1 from tb t where t.mc=tb.mc and t.id<tb.id)
goselect * from tb
/**
id mc
----------- ----
1 aa
2 bb
4 cc
**/
if object_id('tempdb.dbo.#t') is not null drop table #t
go
create table #t([id] int,[mc] varchar(2))
insert #t
select 1,'aa' union all
select 2,'bb' union all
select 3,'aa' union all
select 4,'cc'--方法1:
delete a from #t a where exists(select 1 from #t where [mc]=a.[mc] and [id]<a.[id])--方法2:
delete a from #t a left join (select min([id])[id],[mc] from #t group by [mc]) b on a.[mc]=b.[mc] and a.[id]=b.[id] where b.[id] is null--方法3:
delete a from #t a where [id] not in (select min([id]) from #t where [mc]=a.[mc])--方法4(注:[id]为唯一时可用):
delete a from #t a where [id] not in(select min([id])from #t group by [mc])--方法5:
delete a from #t a where (select count(1) from #t where [mc]=a.[mc] and [id]<a.[id])>0方法6:
delete a from #t a where [id]<>(select top 1 [id] from #t where [mc]=a.[mc] order by [id])方法7:
delete a from #t a where [id]>any(select [id] from #t where [mc]=a.[mc])
DECLARE @tab TABLE(id INT,mc VARCHAR(10))INSERT INTO @tab(id,mc)
SELECT 1,'aa' UNION ALL
SELECT 2,'bb' UNION ALL
SELECT 3,'aa' UNION ALL
SELECT 4,'cc' DELETE FROM @tab
WHERE id not IN (SELECT MIN(t.id) FROM @tab AS t GROUP BY t.mc HAVING COUNT(*) > 1) AND id IN (SELECT t1.id FROM @tab AS t1 INNER JOIN @tab AS t2 ON t1.id <> t2.id AND t1.mc = t2.mc)SELECT * FROM @tab
/*
id mc
----------- ----------
1 aa
2 bb
4 cc
*/