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

暗石绿 2004-08-27 11:06:17
表 ArrearageList ,无主键

mphonecode usrname linkphone

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

如何删除其它相同的记录,只留下一条记录
...全文
162 12 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
bqb 2004-08-27
  • 打赏
  • 举报
回复
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


robin0925 2004-08-27
  • 打赏
  • 举报
回复
学习!
qgbin 2004-08-27
  • 打赏
  • 举报
回复
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
xikboy 2004-08-27
  • 打赏
  • 举报
回复
错了

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


-----要一行一行执行!!
xikboy 2004-08-27
  • 打赏
  • 举报
回复
select distinct * into #kk from ArrearageList
truncate ArrearageList
select * into ArrearageList from #kk
drop table #kk


-----要一行一行执行!!
暗石绿 2004-08-27
  • 打赏
  • 举报
回复
hisi(海山)(随机种子) 的方法我刚试了一下,好像不行。

让我再挨个试试先。
老宛 2004-08-27
  • 打赏
  • 举报
回复
删除重复数据

一、具有主键的情况
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
gaodongsheng 2004-08-27
  • 打赏
  • 举报
回复
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
qgbin 2004-08-27
  • 打赏
  • 举报
回复
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
hisi 2004-08-27
  • 打赏
  • 举报
回复
select distinct * into #t from ArrearageList
delete from ArrearageList
insert into ArrearageList select * from #t
drop table #t
123456754321 2004-08-27
  • 打赏
  • 举报
回复
,
gaodongsheng 2004-08-27
  • 打赏
  • 举报
回复
这个经过测试了,再发一遍:
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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧