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

[woolcool]

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

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

[wzy_love_sly]

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 

1
2
3
5
7
8
9

[wzy_love_sly]

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

[中国风]

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

[woolcool]

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

[-狙击手-]

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]

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呢

[wzy_love_sly]

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

[中国风]

[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]

[parss]

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