Multiple ways to delete duplicate records in SQL

After learning about SQL for a while, I found a lot of duplicate records in the table I built to test (not indexed). Later, we summarize some ways to delete duplicate records, in Oracle, you can delete duplicate records through the unique ROWID, and you can build temporary tables to implement ... This only refers to a few of the simple and practical methods, I hope to share with you (take the table employee as an example).

Sql> DESC Employee

Name Null? Type

----------------------------------------- -------- ------------------

emp_id

Number (10)

Emp_name

VARCHAR2 salary Number (10,2) can query for duplicate records through the following statement:

Sql> SELECT * from employee; emp_id Emp_name SALARY

---------- ---------------------------------------- ----------

1 Sunshine 10000 1 Sunshine 10000 2 Semon 20000 2 SE Mon 20000 3 xyz 30000 2 Semon 20000

Sql> SELECT DISTINCT * from employee;

emp_id emp_namesalary

---------- ---------------------------------------- ----------

1 Sunshine 10000

2 Semon 20000

3 xyz 30000

Sql> SELECT * FROM Employee GROUP BY Emp_id,emp_name,salary has count (*) >1

emp_id Emp_name SALARY

--------- ---------------------------------------- ----------

1 Sunshine 10000

2 Semon 20000

Sql> SELECT * FROM Employee E1 where rowID to (select Max (ROWID) from Employe E2

where e1.emp_id=e2.emp_id and E1.emp_name=e2.emp_name and e1.salary=e2.salary);

emp_id Emp_name SALARY

---------- ---------------------------------------- ----------

1 Sunshine 10000

3 xyz 30000

2 Semon 20000