Find all records with duplicate titles:

SELECT *
FROM t_info a
WHERE ((SELECT COUNT(*)
FROM t_info
WHERE Title = a.Title) > 1)
ORDER BY Title DESCCopy the code

First, find duplicate records

1. Search for all duplicate records

Select * From table Where id = 1 Select * From table Where id = 1 Select * From table Where ID = 1 Select * From table Where ID = 1

2. Filter repeated records (only one is displayed)

Select * From HZT Where ID In (Select Max(ID) From HZT Group By Title)

Note: The maximum ID is displayed here

Delete duplicate records

1. Delete all duplicate records (use with caution)

SQL > Select Count(*) From SQL Where SQL > Select Count(*) From SQL Where SQL > Select Count(*) From SQL Where SQL > Select Count(*) From SQL Where SQL > Select Count(*) From SQL Where SQL > Select Count(*)

2. Keep a record

Delete HZT Where ID Not In (Select Max(ID) From HZT Group By Title)

Note: The maximum ID is reserved here

Delete redundant duplicate records

1. Find redundant duplicate records in the table 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 redundant duplicate records from the table. Duplicate records are judged by a single field (peopleId), and only the records with the smallest ROWID remain

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 table for redundant duplicate records (multiple fields)

select * from vitae a

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

4. Delete redundant duplicate records (multiple fields) from the table and save only the record with the smallest ROWID

delete from vitae a

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. Find redundant duplicate records (multiple fields) in the table, excluding the smallest roWID record

select * from vitae a

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)

Easter egg time: Xiaobian for you to prepare SQL related information, has been backed up with documentation, limited quota first come first served oh

Receive way: only a small attention, private xiaobian micro 【学】 you can get free and practical information