oracle中如何删除重复数据（How to delete duplicate data in Oracle）.doc

oracle中如何删除重复数据（How to delete duplicate data in Oracle） How to delete duplicate data in Oracle We may have this situation, a table originally incomplete design, resulting in the table of data data duplication, then how to delete duplicate data? Duplicate data may have two cases: the first table only has some fields, the second is the two line, the record is exactly the same. Deletion of repeated data for partial fields Lets talk about how to query the duplicated data first. The following statement can query that the data is repeated: Select field 1, field 2, count (*) from, table name group, by field 1, field 2, having, count (*) 1 Change the number to =, you can query the data without duplication. Want to delete these duplicate data?, ^2p, I@u1,}J, Q=? Yh=+~.Fn, TzhTfe, fertility 7 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 very simple, that is, the data you want to delete is deleted. However, the efficiency of such deletion execution is very low, and for large data volumes, the database may be hung. So I suggest inserting the duplicate data from the query into a temporary table, and then deleting it, so you dont have to do a query when youre deleting. As follows: 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 statement is a temporary table, Z V 2? M [}a) for network. Insert the query data into it. The following can be carried out such a deletion operation: Delete from table name a, where field 1, field 2 in (select field 1, field 2 from temporary table); This method of building temporary tables and deleting them is much more efficient than deleting them directly. This time, you may jump out of what? You asked us to execute this statement, isnt that all deleted? And we want to keep the lat