sql语句删除重复的记录(国外英语资料).doc

sql语句删除重复的记录 Delete some fields and repeat the data Lets talk about how to query duplicate data. The following statement can find out which data is duplicated: Select field 1, field 2, count (*) from table name group by field 1, field 2 having count (*) 1 Change the above to the = sign to query the data that is not duplicated. To remove these duplicate data, you can use the following statement to delete: Delete from table name a where field 1, field 2 in (select field 1, field 2, count (*) from table name group by field 1, field 2 having count (*) 1) The above statement is simple to delete the data that is being queried. But this deletion is very inefficient, and the database may be hanged for large data volumes. So I suggest the first query to duplicate data inserted into a temporary table, then to be deleted, so, delete the time dont have to perform a query. As follows: The CREATE TABLE temporary TABLE AS (select field 1, field 2, count (*) from table name group by field 1, field 2 having count (*) 1) The above sentence is to create a temporary table and insert the data that is being queried into it. Heres how to do this: Delete from table name a where field 1, field 2 in (select field 1, field 2 from temporary table); This is much more efficient than simply removing a statement from a temporary table. At this point, you might jump out, what? You asked us to execute this statement, didnt you delete all the duplicates? And we want to keep the latest record in duplicate data! So dont worry, Ill show you how to do that. In Oracle, there is a hidden automatically ROWID, for each record in a unique ROWID, if we want to keep a record of the latest, we can use this field, retain duplicate data in the ROWID biggest one record. Here is an example of query duplicate data: Select a.r.owid, a. * from table name a Where a.r owid! = ( Select Max (b.r. owid) from table name b Where a. field 1 = b. Field 1 and Field 2 = b. Field 2 ) Let me show you that the statement in brac