假设A为主键 declare @tb1 table(A int,B int,C int) insert @tb1 select 2,3,2 union select 1,3,2 union select 7,6,4 delete @tb1 from (select a.A from @tb1 a,@tb1 b where a.B=b.B and a.C=b.C and a.A>b.A) c,@tb1 d where d.A=c.Aselect * from @tb1/* A B C ------------------------ 1 3 2 7 6 4 */
delete a from tablename a where 主键<>( select min(主键) from tablename where col1=a.col1 and col2=a.col2 and ..... )
--比如表T (c1,c2,c3,c4),其中c1是主健,c2,c3,c4有重復紀錄,保留最小c1,其他刪除 delete T where c1 not in (select min(c1) from T a where a.c2=T.c2 and a.c3=T.c3 and a.c4=T.c4)
如果有字段可空,则 delete a from tablename a where 主键<>( select min(主键) from tablename where (col1=a.col1 or col1 is null and a.col1 is null) and (col2=a.col2 or col2 is null and a.col2 is null) and ..... )
declare @t table(id int identity(1,1),name varchar(10)) insert @t(name) select 'a' union all select 'a' union all select 'a' union all select 'b' union all select 'b' union all select 'c' ----保留最小主键行 delete @t where id not in (select min(id) as id from @t group by name ) ----查看 select * from @t
delete from table where id not in(select max(id) from table group by total) --id为主键,totoal为带重复数据的列名!
declare @tb1 table(A int,B int,C int)
insert @tb1
select 2,3,2 union
select 1,3,2 union
select 7,6,4
delete @tb1
from (select a.A from @tb1 a,@tb1 b
where a.B=b.B and a.C=b.C and a.A>b.A) c,@tb1 d
where d.A=c.Aselect * from @tb1/*
A B C
------------------------
1 3 2
7 6 4
*/
from tablename a
where 主键<>(
select min(主键) from tablename
where col1=a.col1
and col2=a.col2
and .....
)
delete T where c1 not in (select min(c1) from T a where a.c2=T.c2 and a.c3=T.c3 and a.c4=T.c4)
delete a
from tablename a
where 主键<>(
select min(主键) from tablename
where (col1=a.col1 or col1 is null and a.col1 is null)
and (col2=a.col2 or col2 is null and a.col2 is null)
and .....
)
insert @t(name)
select 'a' union all
select 'a' union all
select 'a' union all
select 'b' union all
select 'b' union all
select 'c'
----保留最小主键行
delete @t where id not in
(select min(id) as id from @t group by name )
----查看
select * from @t
--id为主键,totoal为带重复数据的列名!