Shulou(Shulou.com)06/01 Report--

I. background

A person table with two fields of id and name. Id is the only one that does not allow repetition, and the same id is considered to be a duplicate record.

II. Solution

Select id from group by id having count (*) > 1

Grouped and counted according to id, if the number of an id number exceeds one, it is considered duplicate.

How to query duplicate data

Select field 1, field 2 from table name group by field 1, field 2 having count (*) > 1

PS: change the > sign above to the = sign to find out the data that is not duplicated.

Oracle removes the SQL of duplicated data (delete all):

Delete the basic structure of duplicate data:

To delete these duplicate data, you can delete them using the following statement

Delete from table name a where field 1, field 2 in (select field 1, field 2 from table name group by field 1, field 2 having count (*) > 1)

Note from the SQL above: the statement is very simple, which is to delete the data from the query. However, this deletion is very inefficient and may hang the database for large amounts of data.

It is recommended that you insert the duplicate data from the query into a temporary table, and then delete it, so that you don't have to do another query when performing the deletion. As follows:

CREATE TABLE temporary table AS (select field 1, field 2 from table name group by field 1, field 2 having count (*) > 1)

The above sentence is to create a temporary table and insert the queried data into it.

The following delete operation can be done:

Delete from table name a where field 1, field 2 in (select field 1, field 2 from temporary table)

Oracle deletes the SQL of duplicate data (leaving a record):

In oracle, there is a hidden automatic rowid that gives each record a unique rowid. If we want to keep the latest record, we can use this field to keep the record with the largest rowid in the duplicated data.

Use ROWID to query duplicate data:

Select a. B.rowid. * from table name a where a.rowid! = (select max (b.rowid) from table name b where a. Field 1 = b. Field 1 and a. Field 2 = b. Field 2)

The SQL in parentheses queries out the largest record in rowid, while the outside queries out other duplicate data except the largest rowid.

Therefore, if we want to delete the duplicate data and keep only the latest piece of data, we can write like this:

Delete duplicate data (leave one of the largest ROWID)

Delete from table name a where a.rowid! = (select max (b.rowid) from table name b where a. Field 1 = b. Field 1 and a. Field 2 = b. Field 2)

Delete duplicate data (leave one of the smallest ROWID)

Delete tab t where t.rowid > (select min (t2.rowid) from tab T2 where t.col2 = t2.col2 and t.col8 = t2.col8)

Of course, the execution efficiency of the above statement is very low, so you can consider establishing a temporary table, saying that you need to determine duplicate fields, insert rowid into the temporary table, and then compare them when you delete them.

Create table temporary table as select a. Field 1 is a. Field 2 max (a.ROWID) dataid from official table a GROUP BY a. Field 1 is a. Field 2; delete from table name a where a.rowid! = (select b.dataid from temporary table b where a. Field 1 = b. Field 1 and a. Field 2 = b. Field 2); commit

Deletion of completely duplicated records

In the case where the two rows of records in the table are exactly the same, you can use the following statement to get the record with deduplicated data:

Select distinct * from table name

You can put the query record into the temporary table, then delete the original table record, and finally import the data from the temporary table back to the original table. As follows:

CREATE TABLE temporary table AS (select distinct * from table name); truncate table formal table; insert into formal table (select * from temporary table); drop table temporary table

If you want to delete duplicate data from a table, you can first create a temporary table, import the deduplicated data into the temporary table, and then import the data into the formal table from the temporary table, as follows:

INSERT INTO t_table_bakselect distinct * from t_table

The method of querying and deleting duplicate records in MySQL

Methods of querying and deleting duplicate records (example demonstration)

1. Look up the redundant duplicate records in the table. Duplicate records are judged by a single field (peopleId).

Select * from table where tableId in (select tableId from table group by tableId having count (tableId) > 1)

2. Delete the redundant duplicate records in the table. The duplicate records are judged according to a single field (tableId), leaving only the records with the smallest rowid.

Delete from table where tableId in (select tableId from table group by tableId having count (tableId) > 1) and rowid not in (select min (rowid) from table group by tableId having count (tableId) > 1)

3. Look up redundant duplicate records in the table (multiple fields)

Select * from vitae a where (a.tableIdmema.seq) in (select tableId,seq from vitae group by tableId,seq having count (*) > 1)

4. Delete the redundant duplicate records (multiple fields) in the table, leaving only the record with the smallest rowid

Delete from vitae a where (a.tableID) in (select tableId,seq from vitae group by tableId,seq having count (*) > 1) and rowid not in (select min (rowid) from vitae group by tableId,seq having count (*) > 1)

5. Look up the redundant duplicate records in the table (multiple fields), excluding the records with the smallest rowid

Select * from vitae a where (a.tableIdmema.seq) in (select tableId,seq from vitae group by tableId,seq having count (*) > 1) and rowid not in (select min (rowid) from vitae group by tableId,seq having count (*) > 1)

6. Query a table where the value of a column is the same SQL:

For example,

There is a field "name" in table A, and the "name" value may be the same between different records.

Now you need to query for items with duplicate "name" values between the records in the table.

Select Name,Count (*) From A Group By Name Having Count (*) > 1

If you also check that the gender is the same, it will be as follows:

Select Name,sex,Count (*) From A Group By Name,sex Having Count (*) > 1

Oracle queries whether there are any duplicates in the fields in the table

Look for a single field:

SELECT TEST_NAME,COUNT (*) FROM T_TEST GROUP BY TEST_NAME HAVING COUNT (*) > 1

Oracle queries duplicate data and deletes it, leaving only one record

1. Look up the redundant duplicate records in the table. Duplicate records are judged by a single field (Id).

Select * from table where Id in (select Id from table group byId having count (Id) > 1)

2. Delete redundant duplicate records from the table. Duplicate records are judged according to a single field (Id), leaving only the records with the smallest rowid.

DELETE from table WHERE (id) IN (SELECT id FROM table GROUP BY id HAVING COUNT (id) > 1) AND ROWID NOT IN (SELECT MIN (ROWID) FROM table GROUP BY id HAVING COUNT (*) > 1)

3. Look up redundant duplicate records in the table (multiple fields)

Select * from table a where (a.Idmema.seq) in (select Id,seq from table group by Id,seq having count (*) > 1)

4. Delete the redundant duplicate records (multiple fields) in the table, leaving only the record with the smallest rowid

Delete from table a where (a.Idmema.seq) in (select Id,seq from table group by Id,seq having count (*) > 1) and rowid not in (select min (rowid) from table group by Id,seq having count (*) > 1)

5. Look up the redundant duplicate records in the table (multiple fields), excluding the records with the smallest rowid

Select * from table a where (a.Idmema.seq) in (select Id,seq from table group by Id,seq having count (*) > 1) and rowid not in (select min (rowid) from table group by Id,seq having count (*) > 1)

For example, there is a personnel table (table name: peosons)

If you want to query the records with the same name, ID number and address

Select p1.*from persons p1 recording p2where p1.idp2.idand p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address

The above effect can be achieved.

Several SQL statements that delete duplicate records

1. Using rowid method

two。 Using group by method

3. Using distinct method

one. Using rowid method

According to the rowid attribute of oracle, you can judge whether there is any duplication. The statement is as follows:

Check the data:

Select * from table1 a where rowid! = (select max (rowid) from table1 b where a.name1=b.name1 and a.name2=b.name2. )

Delete data:

Delete from table1 a where rowid! = (select max (rowid) from table1 b where a.name1=b.name1 and a.name2=b.name2. )

2.group by method

Check the data:

Select count (num), max (name) from student

-- lists the number of duplicate records and lists his name attribute

Group by num

Having count (num) > 1-after grouping by num, find out the repetition of the numb column in the table, that is, the number of occurrences is more than one.

Delete data:

Delete from studentgroup by numhaving count (num) > 1

In that case, all duplicates will be deleted.

3. Use the distinct method-useful for small tables

Create table table_new as select distinct * from table1 minuxtruncate table table1;insert into table1 select * from table_new

Methods of querying and deleting duplicate records

1. Look up the redundant duplicate records in the table. Duplicate records are judged by a single field (peopleId).

Select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count (peopleId) > 1)

2. Delete redundant duplicate records from the table. Duplicate records are judged according to a single field (peopleId), leaving only the records with the smallest rowid.

Delete from peoplewhere peopleId in (select peopleId from people group by peopleIdhaving count (peopleId) > 1) and rowid not in (select min (rowid) from people group by peopleIdhaving count (peopleId) > 1)

3. Look up redundant duplicate records in the table (multiple fields)

Select * from vitae awhere (a.peopleIdmema.seq) in (select peopleId,seq from vitae group by peopleId,seq having count (*) > 1)

4. Delete the redundant duplicate records (multiple fields) in the table, leaving only the record with the smallest rowid

Delete from vitae awhere (a.peopleIdline a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count (*) > 1) and rowid not in (select min (rowid) from vitae group by peopleId,seq having count (*) > 1)

5. Look up the redundant duplicate records in the table (multiple fields), excluding the records with the smallest rowid

Select * from vitae awhere (a.peopleIdmeme a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count (*) > 1) and rowid not in (select min (rowid) from vitae group by peopleId,seq having count (*) > 1)

(2)

For example,

There is a field "name" in table A

And the "name" value may be the same between different records.

Now you need to query for items with duplicate "name" values between the records in the table.

Select Name,Count (*) From A Group By Name Having Count (*) > 1

If you also check that the gender is the same, it will be as follows:

Select Name,***,Count (*) From A Group By Name,*** Having Count (*) > 1

(III)

Method one

Declare @ max integer,@id integerdeclare cur_rows cursor local for select main field, count (*) from table name group by main field having count (*) >; 1open cur_rowsfetch cur_rows into @ id,@maxwhile @ @ fetch_status=0beginselect @ max = @ max-1set rowcount @ maxdelete from table name where main field = @ idfetch cur_rows into @ id,@maxendclose cur_rowsset rowcount 0

Method two

"duplicate record" has two duplicate records, one is a completely duplicate record, that is, a record in which all fields are duplicated, and the other is a record in which some key fields are duplicated.

For example, the Name field is duplicated, while other fields are not necessarily duplicated or can be ignored.

1. For the first kind of repetition, it is easier to solve and use

Select distinct * from tableName can get the result set without duplicate records.

If the table needs to delete duplicate records (keep 1 duplicate record), you can delete it as follows

Select distinct * into # Tmp from tableNamedrop table tableNameselect * into tableName from # Tmpdrop table # Tmp

The reason for this repetition is the poor design of the table, which can be solved by adding unique index columns.

2. This kind of duplicate problem usually requires the retention of the first record in the duplicate record. The operation method is as follows.

Suppose a duplicate field is Name,Address, and a unique result set of these two fields is required.

Select identity (int,1,1) as autoID, * into # Tmp from tableNameselect min (autoID) as autoID into # Tmp2 from # Tmp group by Name,autoIDselect * from # Tmp where autoID in (select autoID from # tmp2)

The last select results in a result set in which Name,Address does not repeat (but with an extra autoID field, which can be omitted in the select clause when actually writing)

(4) duplicate queries

Select * from tablename where id in (select id from tablenamegroup by idhaving count (id) > 1)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.