SQL Delete duplicate data method

One, part of the same field to remove duplicate data method:

By grouping group by repeating field the having (*) >1 condition--"Extract the duplicate field value--" Finally, based on the uniqueness of the ID--Get the value of min or Max ID in the duplicated data--"and then query the original table for duplicate data--" Duplicate data can generate a new table, then a new table with non-repeating data, two table data together

Example 1:

Select MIN (ID) from Usersloginlog where username= ' ceshi06 ' and Convert (varchar (ten), logtime,120) in (
Select Convert (varchar), logtime,120) as Dayt from Usersloginlog
where Username= ' ceshi06 ' GROUP by Convert (varchar (), logtime,120) has COUNT (*) >1
)
GROUP BY Convert (varchar), logtime,120

Example 2:

Select min (ID) from Usersloginlog where Username= ' ceshi06 ' GROUP by Convert (varchar (+), logtime,120) having COUNT (*) ; 1

Two: Here is a more comprehensive example: there is time to do more research

For example:
ID Name Value
1 a pp
2 a PP
3 B III
4 B pp
5 B pp
6 C pp
7 C pp
8 C III
ID is primary key
Ask for such a result
ID Name Value
1 a pp
3 B III
4 B pp
6 C pp
8 C III

Method 1
Delete yourtable
where [id] not in (
Select MAX ([id]) from yourtable
Group BY (name + value))

Method 2
Delete a
From Table a LEFT join (
Select Id=min (ID) from table GROUP by Name,value
) B on A.id=b.id
where b.id is null



SQL statement for querying and deleting duplicate records

1, find redundant duplicate records in the table, duplicate records are based on a single field (Peopleid) to determine the
Select * from people
where Peopleid in (select   Peopleid from   People group by   Peopleid having count (Peopleid) > 1)
2, delete redundant records from the table, Duplicate records are judged by a single field (Peopleid), leaving only the ROWID minimum record
Delete from people
where Peopleid in (select   Peopleid from People group by   peopleid   have count (Peopleid) > 1)
and rowID not in (select min (rowid) fro m   People GROUP by Peopleid have Count (Peopleid) >1)
3, extra duplicate records in lookup table (multiple fields)
Select * from Vitae a
W Here (A.PEOPLEID,A.SEQ) in   (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)
4, Delete extra duplicate records (multiple fields) in the table, leaving only the ROWID minimum record
Delete from Vitae a
where (A.PEOPLEID,A.SEQ) in   (select Peopleid, Seq from vitae GROUP by PEOPLEID,SEQ have count (*) > 1)
and rowID not in (select min (rowid) from Vitae GROUP by PE Opleid,seq having Count (*) >1)

5. Find redundant duplicate records (multiple fields) in the table, not including the smallest ROWID records
SELECT * FROM Vitae a
where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)
and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)
Two
Say
A field "name" exists in table A,
and the "name" value may be the same between different records,
Now is the need to query out the records in the table, "name" value has duplicate entries;
Select Name,count (*) from A Group by Name have Count (*) > 1
If you also look at the same gender, the following is true:
Select Name,sex,count (*) from A Group by Name,sex have Count (*) > 1

Three
Method One
Declare @max integer, @id integer
Declare cur_rows cursor Local for select main field, COUNT (*) from table name Group by main field having count (*) >; 1
Open Cur_rows
Fetch cur_rows into @id, @max
While @ @fetch_status =0
Begin
Select @max = @max-1
SET ROWCOUNT @max
Delete from table name where main field = @id
Fetch cur_rows into @id, @max
End
Close Cur_rows
SET ROWCOUNT 0

Method Two
"duplicate record" has two meanings of duplicate records, one is a completely duplicate record, that is, all fields are duplicates of the record .
1, for the first repetition, it is easier to solve, using the
SELECT DISTINCT * from TableName
can be a result set without duplicate records.
If the table needs to delete duplicate records (duplicate records retain 1), you can delete the
select DISTINCT * into #Tmp the FROM TableName
drop table tableName
SELECT * into TableName
drop table #Tmp
This duplication occurs because the table is poorly designed and the unique index columns are added to resolve.
2. This type of repetition usually requires the first record in the duplicate record to be retained, as follows
Assuming that there is a duplicate field name,address, which requires a result set that is unique to both fields
Select Identity (int,1,1) as Autoid, * to #Tmp from TableName
Select min (autoid) as autoid to #Tmp2 from #Tmp Group by name,autoid
Select * F Rom #Tmp where autoid in (select Autoid from #tmp2)
The last select is the result set that name,address not duplicate (but one more autoid field, The actual write can be written in the SELECT clause to omit this column)
(iv)
query repeats
SELECT * FROM tablename where ID in (
Select ID from tablename
GROUP by id< Br>having count (ID) > 1
)