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

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

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


1
2
3
5
7
8
9
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-01-04 09:53
社区公告
暂无公告