全部
基础类
应用实例
新技术前沿

高手请进，赐教。急急急急急急急急~~~

woolcool 南京灵耀网络科技有限公司软件工程师 2008-01-04 09:53:18

如果表中有1000条记录，
但是只有一百条记录是有效的，其他的都是重复记录，
高手赐教，如何用sql语句删除重复记录

重复记录在表中是无序的，位置不固定，
此问题困扰好长时间了
高手赐教

...全文

39 点赞收藏 9

写回复

9 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

parss 2008-01-05

把表结构贴出来吧，不然不好写

回复赞

中国风 2008-01-04

[code=SQL]create  table  T (id int)

insert into T select 1

insert into T select 2

insert into T select 3

insert into T select 5

insert into T select 5

insert into T select 7

insert into T select 7

insert into T select 8

insert into T select 9



go



select * from T  a where (select count(1) from T where ID=a.ID)>1--显示重复记录

/*

id          

----------- 

5

5

7

7



（所影响的行数为 4 行）



*/



--删除

go

select distinct  *  into # from T



truncate table T



insert T select * from #



--查看

select * from T



/*

id          

----------- 

1

2

3

5

7

8

9



（所影响的行数为 7 行）



*/

[/code]

回复赞

wzy_love_sly 2008-01-04

有标识列容易删除，没有就利用临时表删除

回复赞

woolcool 2008-01-04

Create Table Num
(
num number;
)
;

insert into Num values(1);
insert into Num values(2);
insert into Num values(3);
insert into Num values(5);
insert into Num values(5);
insert into Num values(7);
insert into Num values(7);
insert into Num values(8);
insert into Num values(9);

这样怎么查询，不用虚拟表，就是用上边的这个真实得表，要是不用count呢

回复赞

-狙击手- 2008-01-04

declare @tb table (id int)

insert into @tb select 1

insert into @tb select 2

insert into @tb select 3

insert into @tb select 5

insert into @tb select 5

insert into @tb select 7

insert into @tb select 7

insert into @tb select 8

insert into @tb select 9



select *

from @tb a

where exists (select id from @tb where a.id = id group by id having count(1) > 1)



/*



id          

----------- 

5

5

7

7



（所影响的行数为 4 行）

*/



select *

from @tb a

where id in (select id from @tb group by id having count(1) > 1)







/*



id          

----------- 

5

5

7

7



（所影响的行数为 4 行）

*/

回复赞

woolcool 2008-01-04

不太懂，如何删除多余的重复记录

回复赞

中国风 2008-01-04

--表T列重复有Col1\Col2
alter table T add ID int identity --新增一列
go

delete a from T a where exists(select 1 from T where Col1=a.COl1 and COl2=a.Col2 and ID<a.ID)
go
alter table drop column ID--删除新增列

---
如没有外健关联时:

select distinct * into # from T

truncate table T

insert T select * from #

回复赞

wzy_love_sly 2008-01-04

declare @tb table (id int,name varchar(10))

insert into @tb select 1,'a'

insert into @tb select 2,'b'

insert into @tb select 3,'c'

insert into @tb select 5,'d'

insert into @tb select 5,'d'

insert into @tb select 7,'e'

insert into @tb select 7,'e'

insert into @tb select 8,'f'

insert into @tb select 9,'g'



select * from @tb group by id,name

1 a
2 b
3 c
5 d
7 e
8 f
9 g

回复赞

wzy_love_sly 2008-01-04

declare @tb table (id int)

insert into @tb select 1

insert into @tb select 2

insert into @tb select 3

insert into @tb select 5

insert into @tb select 5

insert into @tb select 7

insert into @tb select 7

insert into @tb select 8

insert into @tb select 9



select distinct id from @tb a