SQL statement for deleting duplicate records

SQL statement for deleting duplicate records
Delete duplicate records. The answer to this question is as follows:

Select distinct * into Temp from Student
Truncate table Student
Insert Student select * from Temp
Drop table Temp
-- Duplicate data has been deleted.
-- Display the deleted result
Select * from student
The idea is to first query the non-duplicate data and save it to a temporary table Temp, then use the truncate keyword to delete the data content of the target table, and finally use the drop command to delete the Temp table.

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

The non-recursive C # code solution of the Fibonacci series is as follows:

Static Int64 Fib (Int64 n)
{
Int first, second, third;
First = second = third = 1;
If (n = 1 | n = 2)
     {
Return 1;
     }
For (int I = 3; I <= n; I ++)
     {
First = second + third;
Third = second;
Second = first;
     }
Return first;
}
Fibonacci (Fibonacci) sequence:
Fib (n) = Fib (n-1) + Fib (n-2), n> 1, Fib (1) = Fib (2) = 1
That is, the first and second items of the sequence are 1, starting from the third item, and the last one is the sum of the first two items.
The first eight items of the sequence are:
1, 1, 2, 3, 5, 8, 13, 21

More and better methods

If you want to query records whose names, ID card numbers, and addresses are identical
Select p1. * from persons p1, persons p2 where p1.id <> p2.id and p1.cardid = p2.cardid and p1.pname = p2.pname and p1.address = p2.address

The above results can be achieved.

Several SQL statements used to delete repeated records
 
1. Use the rowid method

2. Use the group by method

3. Use the distinct method

1. Rowid method

The statement is as follows:

Query 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

Query data:

Select count (num), max (name) from student -- lists the number of repeated Records and its name attribute
Group by num
Having count (num)> 1 -- Group by num to find the num column in the table, that is, more than once

Delete data:

Delete from student
Group by num
Having count (num)> 1
In this way, all duplicates are deleted.

3. Use the distinct method-useful for small tables

 

Create table table_new as select distinct * from table1 minux
Truncate table table1;
Insert into table1 select * from table_new;

How to query and delete duplicate records

1. Search for redundant duplicate records in the table. Duplicate records are determined based on a single field (peopleId).
Select * from people
Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)

2. Delete unnecessary duplicate records in the table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid
Delete from people
Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)
And rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1)

3. Search for redundant duplicate records in the table (multiple fields)
Select * from vitae
Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1)

4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid
Delete from vitae
Where (a. peopleId, 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. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid
Select * from vitae
Where (a. peopleId, 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)