In SQL, the statement that only one piece is left for deduplicating records is preferably explained

1 --Oracle deduplicates records, which can be operated using Oracle's unique rowid such as:

delete from test1 where rowid not in (select max(rowid) from test1);

Explanation: rowid is a virtual column, not a real column in the table, using the unique feature of rowid, you can remove duplicates.

Recorded. 2 --mysql deduplicates the records.

Idea: Use the distinct keyword to obtain unique records, and then create a new table to copy the records.

1、create table test2 as select distinct * from test1;

2、drop table test1;

3、alter table test2 rename to test1;

Explanation: Create a new table to copy the unique records, delete the original table, and rename the new table to the original table name. Finish.

This can't be done in one step.

You can find out the unique information and put it in the temporary table first.

select distinct *

into #a

from table1

Delete the original table.

delete from table1

Insert the information back into the original table.

insert into table1

select * from #a

Use the distinct keyword to return a uniquely different record.

Syntax: select distinct column name from table name.

1. You can use the following statements to deal with it, and you can do it according to the annotations:

Deduplicate the data and stage it in temporary table A.

select distinct * into a from table1 where condition.

delete table1 where --Note: Insert into table1 select * from a -- Insert into table1 select * from a -- Insert the staged data back into the database.

drop table a -- Delete the temporary table.

2. In addition, in the current database, it is recommended that each table should have a flag field to ensure that the records are not completely duplicated, otherwise it is very easy to have problems in practice.

Add an auto-incrementing column to the table, then get the larger column through the statement and delete it.