3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
高手请进，赐教。~~~~急急急急急急急急~~~~~~~
如果表中有1000条记录，
但是只有一百条记录是有效的，其他的都是重复记录，
高手赐教，如何用sql语句删除重复记录
重复记录在表中是无序的，位置不固定，
此问题困扰好长时间了
高手赐教
但是只有一百条记录是有效的，其他的都是重复记录，
高手赐教，如何用sql语句删除重复记录
重复记录在表中是无序的，位置不固定，
此问题困扰好长时间了
高手赐教
...全文
当前发帖距今超过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 行）
*/
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呢
(
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 #
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,name1 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 1
2
3
5
7
8
9
