select distinct * into #t from ArrearageList delete ArrearageList from ArrearageList a,#t b where a.mphonecode=b.mphonecode and a.usrname=b.usrname and a.linkphone=b.linkphone insert into ArrearageList select * from #t drop table #t
alter table ArrearageList add a int declare @i int set @i = 0 update ArrearageList set a=@i,@i=@i+1 delete from ArrearageList where a not in(select max(a) from ArrearageList group by mphonecode,usrname,linkphone) alter table ArrearageList drop column a
删除重复数据一、具有主键的情况 a.具有唯一性的字段id(为唯一主键) delete table where id not in ( select max(id) from table group by col1,col2,col3... ) group by 子句后跟的字段就是你用来判断重复的条件,如只有col1, 那么只要col1字段内容相同即表示记录相同。b.具有联合主键 假设col1+','+col2+','...col5 为联合主键 select * from table where col1+','+col2+','...col5 in ( select max(col1+','+col2+','...col5) from table where having count(*)>1 group by col1,col2,col3,col4 ) group by 子句后跟的字段就是你用来判断重复的条件, 如只有col1,那么只要col1字段内容相同即表示记录相同。c:判断所有的字段 select * into #aa from table group by id1,id2,.... delete table insert into table select * from #aa二、没有主键的情况a:用临时表实现 select identity(int,1,1) as id,* into #temp from ta delete #temp where id not in ( select max(id) from # group by col1,col2,col3... ) delete table ta inset into ta(...) select ..... from #tempb:用改变表结构(加一个唯一字段)来实现 alter table 表 add newfield int identity(1,1) delete 表 where newfield not in ( select min(newfield) from 表 group by 除newfield外的所有字段 )alter table 表 drop column newfield
hisi(海山)(随机种子) 的方法我刚试了一下,好像不行。让我再挨个试试先。
select distinct * into #kk from ArrearageList truncate ArrearageList select * into ArrearageList from #kk drop table #kk -----要一行一行执行!!
错了select distinct * into #kk from ArrearageList truncate ArrearageList insert into ArrearageList select * from #kk drop table #kk -----要一行一行执行!!
create table ArrearageList ( mphonecode char (15),usrname char (4),linkphone char (15))insert into ArrearageList select '13321223373','A','13919293344' insert into ArrearageList select '13321223373','A','13919293344' insert into ArrearageList select '13321223373','A','13919293344' insert into ArrearageList select '13321223373','A','13919293344' insert into ArrearageList select '13321223373','A','13919293344'select distinct * into #t from ArrearageList delete ArrearageList from ArrearageList a,#t b where a.mphonecode=b.mphonecode and a.usrname=b.usrname and a.linkphone=b.linkphone insert into ArrearageList select * from #t drop table #t select * from ArrearageList mphonecode usrname linkphone ------------- -------- --------------- 13321223373 A 13919293344
create table KK(mphonecode bigint,usrname varchar(2),linkphone bigint)insert into kk select 13321223373,'A',13919293344 union all select 13321223373,'A',13919293344 union all select 13321223373,'A',13919293344 union all select 13321223373,'A',13919293344select * from KKselect distinct * into KK_2 from KKdrop table KKexec sp_rename KK_2,kkselect * from KK --结果 mphonecode usrname linkphone ------------- -------- --------------- 13321223373 A 13919293344 13321223373 A 13919293344 13321223373 A 13919293344 13321223373 A 13919293344 mphonecode usrname linkphone ------------- -------- --------------- 13321223373 A 13919293344
这个经过测试了,再发一遍: alter table ArrearageList add a int declare @i int set @i = 0 update ArrearageList set a=@i,@i=@i+1 delete from ArrearageList where a not in(select max(a) from ArrearageList group by mphonecode,usrname,linkphone) alter table ArrearageList drop column a
delete ArrearageList from ArrearageList a,#t b where a.mphonecode=b.mphonecode and a.usrname=b.usrname and a.linkphone=b.linkphone
insert into ArrearageList select * from #t
drop table #t
declare @i int
set @i = 0
update ArrearageList set a=@i,@i=@i+1
delete from ArrearageList
where a not in(select max(a) from ArrearageList group by mphonecode,usrname,linkphone)
alter table ArrearageList drop column a
a.具有唯一性的字段id(为唯一主键)
delete table
where id not in
(
select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用来判断重复的条件,如只有col1,
那么只要col1字段内容相同即表示记录相同。b.具有联合主键
假设col1+','+col2+','...col5 为联合主键
select * from table where col1+','+col2+','...col5 in (
select max(col1+','+col2+','...col5) from table
where having count(*)>1
group by col1,col2,col3,col4
)
group by 子句后跟的字段就是你用来判断重复的条件,
如只有col1,那么只要col1字段内容相同即表示记录相同。c:判断所有的字段
select * into #aa from table group by id1,id2,....
delete table
insert into table
select * from #aa二、没有主键的情况a:用临时表实现
select identity(int,1,1) as id,* into #temp from ta
delete #temp
where id not in
(
select max(id) from # group by col1,col2,col3...
)
delete table ta
inset into ta(...)
select ..... from #tempb:用改变表结构(加一个唯一字段)来实现
alter table 表 add newfield int identity(1,1)
delete 表
where newfield not in
(
select min(newfield) from 表 group by 除newfield外的所有字段
)alter table 表 drop column newfield
truncate ArrearageList
select * into ArrearageList from #kk
drop table #kk
-----要一行一行执行!!
truncate ArrearageList
insert into ArrearageList select * from #kk
drop table #kk
-----要一行一行执行!!
select '13321223373','A','13919293344'
insert into ArrearageList
select '13321223373','A','13919293344'
insert into ArrearageList
select '13321223373','A','13919293344'
insert into ArrearageList
select '13321223373','A','13919293344'
insert into ArrearageList
select '13321223373','A','13919293344'select distinct * into #t from ArrearageList
delete ArrearageList from ArrearageList a,#t b
where a.mphonecode=b.mphonecode and a.usrname=b.usrname and a.linkphone=b.linkphone
insert into ArrearageList select * from #t
drop table #t
select * from ArrearageList mphonecode usrname linkphone
------------- -------- ---------------
13321223373 A 13919293344
select 13321223373,'A',13919293344
union all select 13321223373,'A',13919293344
union all select 13321223373,'A',13919293344
union all select 13321223373,'A',13919293344select * from KKselect distinct * into KK_2 from KKdrop table KKexec sp_rename KK_2,kkselect * from KK
--结果 mphonecode usrname linkphone
------------- -------- ---------------
13321223373 A 13919293344
13321223373 A 13919293344
13321223373 A 13919293344
13321223373 A 13919293344
mphonecode usrname linkphone
------------- -------- ---------------
13321223373 A 13919293344
alter table ArrearageList add a int
declare @i int
set @i = 0
update ArrearageList set a=@i,@i=@i+1
delete from ArrearageList
where a not in(select max(a) from ArrearageList group by mphonecode,usrname,linkphone)
alter table ArrearageList drop column a