删除表中重复数据的方法

--删除表中的重复记录。

create table user_info

(

       username varchar2(20),

       password varchar2(20)

)

 

insert into user_info values('JACK','123',SQ_USER.NEXTVAL);

insert into user_info values('JACK','123',SQ_USER.NEXTV);

insert into user_info values('SHININ','123',SQ_USER.NEXTVAL);

insert into user_info values('SHININ','123',SQ_USER.NEXTVAL);

insert into user_info values('SHININ','456',SQ_USER.NEXTVAL);

 

--方案一

CREATE TABLE  USER_INFO2

AS

SELECT DISTINCT * FROM USER_INFO ;

 

DROP TABLE USER_INFO;

 

RENAME USER_INFO2 TO USER_INFO;

 

SELECT * FROM USER_INFO;

 

--方案二

ALTER TABLE USER_INFO ADD ID NUMBER;

CREATE SEQUENCE SQ_USER;

DELETE FROM USER_INFO;

 

DELETE FROM USER_INFO

       WHERE ID NOT IN(

             SELECT MIN(ID)AS ID FROM USER_INFO

             GROUP BY (USERNAME,PASSWORD));