Question

SQL中用什么语句去掉表中重复的元组

避如下面有两条相同元组:
01 Michael math
02 Jenny English
01 Michael math

Answer 1

删不了。

最好的方法是把ID那列做一个主键，这样在插入时就不会有重复的。在显示的时候倒是可以。

Answer 2

一般的数据库设计里都是不会存在重复行的，都有个主键约束或唯一约束什么的，sql语句不大好弄，在企业管理器中倒是好办

Answer 3

create table mytab( id varchar2(2), name varchar2(20), name2 varchar2(20))
insert into mytab
select '01','Michael', 'math' from dual
union all
select '02','Jenny', 'English' from dual
union all
select '01','Michael', 'math' from dual
select distinct * from mytab

Answer 4

select
name,ip,min(time)
from
table
group
by
name,ip

Answer 5

1、将重复的记录记入temp1表:
select [标志字段id],count(*) into temp1 from [表名]
group by [标志字段id]
having count(*)>1
2、将不重复的记录记入temp1表:
insert temp1
select [标志字段id],count(*) from [表名]
group by [标志字段id]
having count(*)=1
3、作一个包含所有不重复记录的表：
select * into temp2 from [表名]
where 标志字段id in(select 标志字段id from temp1)
4、删除重复表:delete [表名]
5、恢复表：
insert [表名]
select * from temp2
6、删除临时表:
drop table temp1
drop table temp2