del Repeat number

Landlord posted: 2010-06-21 11:46:31

This post was last edited by Luckycynthia on 2010-06-21 11:47:46

On the way to manipulate the data after fetching the data, sometimes encounter duplicate data, repeated data sometimes causes the database part of the settings can not be set correctly, so it is necessary to filter.

First, there are two meanings of duplicate records, one is a completely duplicate record, that is, all fields are duplicated records, and the second is some key field duplicate records, such as the Name field repeats, and other fields may not be repeated or can be ignored.

For the first kind of repetition, it is easier to solve, using:

SELECT DISTINCT * from TableName

You can get a result set with no duplicate records.

If the table needs to delete duplicate records (duplicate records retain 1), you can delete them as follows:

SELECT DISTINCT * to #Tmp from TableName

drop table TableName

SELECT * Into TableName from #Tmp

drop table #Tmp

For the second repetition, we need to use the following method, assuming that the repeating field is mobile and unitname:

SELECT * FROM Tablenam

where [Mobile] in (SELECT [Mobile] with Info_user_a GROUP BY [Mobile] has count ([Mobile]) >1)

and ID not in (the Select min (id) from Info_user_a GROUP by [Mobile] has count ([Mobile]) >1)

and [Unitname] in (SELECT [Unitname] from Info_user_a GROUP by [Unitname] have count ([Unitname]) >1)

and ID not in (the Select min (id) from Info_user_a GROUP by [Unitname] has count ([Unitname]) >1)

This is to get duplicate data, if you want to delete, just change the starting select * to delete.

Next is the focus, if the database is really 10 million, or tens of millions, do not directly to the entire library to operate, the better way is to divide a library into multiple, such as the original 1000W, divided into 10 100W, and then respectively on the 10 100W library operation, but if there is really tens of millions of data, It's better to use the Oracle database instead, I feel a bit overwhelmed with SQL Server 2008, and it's a dual core core CPU, maybe a little bit of memory, just 2 G.

del Repeat number