如何删除其它相同的记录，只留下一条记录

[暗石绿]

表 ArrearageList ，无主键

mphonecode usrname linkphone

13321223373 A 13919293344
13321223373 A 13919293344
13321223373 A 13919293344
13321223373 A 13919293344

如何删除其它相同的记录，只留下一条记录

[gaodongsheng]

这个经过测试了,再发一遍:
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

[123456754321]

,

[hisi]

select distinct * into #t from ArrearageList
delete from ArrearageList
insert into ArrearageList select * from #t
drop table #t

[qgbin]

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

[gaodongsheng]

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 #temp

b:用改变表结构（加一个唯一字段）来实现
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(海山)(随机种子) 的方法我刚试了一下，好像不行。

让我再挨个试试先。

[xikboy]

select distinct * into #kk from ArrearageList
truncate ArrearageList
select * into ArrearageList from #kk
drop table #kk


-----要一行一行执行！！

[xikboy]

错了

select distinct * into #kk from ArrearageList
truncate ArrearageList
insert into ArrearageList select * from #kk
drop table #kk


-----要一行一行执行！！

[qgbin]

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

[robin0925]

学习!

[bqb]

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',13919293344

select * from KK

select distinct * into KK_2 from KK

drop table KK

exec sp_rename KK_2,kk

select * from KK


--结果

mphonecode usrname linkphone
------------- -------- ---------------
13321223373 A 13919293344
13321223373 A 13919293344
13321223373 A 13919293344
13321223373 A 13919293344


mphonecode usrname linkphone
------------- -------- ---------------
13321223373 A 13919293344