怎样删除c3、c4重复的数据但要留下其中任意一条
表T1结构和数据如下,
其中c1、c2为主键
发现有重复数据.
c1 c2 c3 c4
1 1 3 4
1 2 3 4
1 3 1 3
1 4 1 3
1 5 1 3
怎样删除c3、c4重复的数据但要留下其中任意一条。
select * from dbo.jj_djjs
create table aaa(c1 char(1) ,c2 char(1) )
insert into aaa values ('1','1' )
insert into aaa values ('1','1' )
insert into aaa values ('1','3' )
insert into aaa values ('1','4' )
insert into aaa values ('1','5' )
insert into aaa values ('1','5' )
select * from aaa
select * from aaa a
where ( select count(1) FROM aaa
where c1=a.c1 and a.c2=c2 ) <=1
order by c1,c2
drop table aaa
--完全重复
select distinct * into #t from tablename
delete from tablename
insert into tablename select * from #t
drop table #t
--除主键外重复,保留最大的主键
select * from aaa a
where exists(
select 1 from aaa where c1 +c2 > a.c1 +a.c2
and c3 = a.c3 and c4=a.c4)
select * from aaa
where c1+c2 not in (
select min(c1+c2) from aaa group by c3+c4 )