SQL de-Redo statement

from: http://www.cnblogs.com/crazybottle/p/3928139.html

Huge amount of data (more than million), some of which are all the same, some of the same field, how to effectively remove duplication?

If you want to delete the phone (mobilephone), Phone (Officephone), mail (email) at the same time the same data, has been used this statement for deduplication:

Delete  fromTablewhereId not inch(Select Max(ID) fromTableGroup  bymobilephone,officephone,email)orDelete  fromTablewhereId not inch(Select min(ID) fromTableGroup  byMobilephone,officephone,email)

One of the following will be slightly faster. Above this data for 1 million of data efficiency can also, repeat the number of 1/5 in the case of a few minutes to a few 10 minutes, but if the amount of data reached more than 3 million, efficiency dips, if repeated data more points, often dozens of hours to run, and sometimes lock the table run a night can not finish. Helpless had to re-search for new viable methods, and today finally some gains:

// Query the ID of the unique data and import them into the temp table tmp Select min  as  into  from Group  by Mobilephone,officephone,email // query out the heavy data and insert it into the finally table Insert  into Select  from where inch (Select from TMP)

Efficiency comparison: Use Delete method to 5 million data deduplication (1/2 repetition) about 4 hours. 4 hours, a long time.

Use temporary tables to insert 5 million data deduplication (1/2 repetitions) in less than 10 minutes.

SQL statement removes duplicate records, gets duplicate records

Finding duplicate data for these fields in a table based on some field names and deleting them as they were inserted depends on order by and Row_num.

Method One is repeated with multiple conditions:

Delete  from (  Select=over byorderbydesc)    fromwhere time >getdate()-1) TMPwhere>1

Method two to remove the weight according to a single condition:

Delete  from where  not inch (  Selectmaxfromgroupby hascount (Need to go to the heavy field) >= 1 )

Note: In order to improve the efficiency as above two methods can use temporary table, not in the table can first extract temporary table #tmp,

And then use not exists to do, in order to avoid excessive quantity, can be used to control the volume of the deletion of top

Delete Top (2 from table where   not exists (Select  primary key ID  fromwhere #tmp. primary Key ID=

-------------------->>>