Oracle removes duplicate record statements

Oracle removes duplicate record statements

  For example, there is now a table (table name: peosons) If you want to name, social Security number, address the three fields exactly the same record query select p1.*  from persons  p1,persons &NBSP;P2  where p1.id<>p2.id  and  p1.cardid = p2.cardid and P1.pname = p2.pname and p1.address = P2.ADDRESS&N BSP; You can achieve the above effects.    www.2cto.com   Several SQL statement  1 to delete duplicate records.  2 with the rowID method.  3 by method Distinct. Use the rowID method   According to the ROWID attribute of the Oracle band to determine if there are duplicates, the statements are as follows:  data:      select * FROM table1 a where rowID! = (select Max (ROWID)      from table1 b where a.name1=b.name1 and a.name2=b.name2 ...)   Delete data:     Delete  from table1 a where rowid! = (select Max (ROWID)     from table1 b where A.name1=b.name1 and a.name2=b.name2 ...)  2.group by method   data:  select COUNT (num), max (name) from student--Lists the number of duplicate records and lists his name attribute group by NUM has COUNT (num) >1--Grouping num columns by num to find duplicates in the table, that is, more occurrences than one   delete data:  Delete from student GROUP by NUM have count (num) > 1 in this case, all the heavyThe duplicate has been removed.  3. Using the distinct method-useful for small tables  create table table_new as   SELECT DISTINCT *   from table1 minuxtruncate ta ble table1;insert to table1 select * from table_new;  query and delete duplicate records method Daquan  1, lookup table redundant duplicate records, Duplicate records are based on a single field (Peopleid) to judge select * from Peoplewhere peopleid in (select Peopleid from People GROUP by Peopleid have count ( Peopleid) > 1)  2, delete redundant records in the table, repeat records are based on a single field (Peopleid) to judge, leaving only the rowid smallest record delete from Peoplewhere Peopleid in ( Select Peopleid from People GROUP by Peopleid  having count (Peopleid) > 1) and rowID not in (select min (rowid) from People GROUP by Peopleid have Count (Peopleid) >1)  3, redundant duplicate records in lookup table (multiple fields) SELECT * from Vitae awhere (a.peopleid,a.s eq) in (select Peopleid,seq to Vitae GROUP by PEOPLEID,SEQ have count (*) > 1)  4, delete redundant duplicate records (multiple fields) in the table, leaving only the smallest rowid Record Delete from Vitae awhere (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)  5, redundant duplicate records in lookup table (multiple fields), do not contain ROWID minimum records select * from Vitae awhere (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)   (ii) For example, there is a field "name" in Table A, and the "name" value between different records may be the same, 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 gender is also the same large then the following: Select Name,sex,count (*) from a GROUP by Nam E,sex having Count (*) > 1 (three) method one declare @max integer, @id integerdeclare cur_rows cursor Local for select main field, Count (*) F ROM table name group by main field have count (*) >; 1open cur_rowsfetch cur_rows into @id, @maxwhile @ @fetch_status =0beginselect @max = @max -1set rowcount @maxdelete from table name where main field = @idfetch cur_rows into @id, @maxendclose cur_rowsset RowCount 0 method two "heavy Duplicate records "There are two meanings of repeating records, one is a completely duplicate record, that is, all the fields are duplicated records, and the second is some key field duplicate records, such as the Name field is repeated, and the other fields may not repeat or repeat can be ignored. 1, for the first kind of 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 them as follows
SELECT DISTINCT * to #Tmp from Tablenamedrop table Tablenameselect * to tableName from #Tmpdrop table #Tmp发生这种重复的原因是表设 A unique index column can be resolved. 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 Tablenameselect min (autoid) as autoid to #Tmp2 from #Tmp Group by N Ame,autoidselect * from #Tmp where autoid in (select Autoid from #tmp2) The last select is the result set name,address not duplicated (but one more autoid field, real Write in the SELECT clause to omit this column) (four) query duplicate select * FROM tablename where ID in (select ID from Tablenamegroup by idhaving count (ID) > 1) Example Delete from W_m_mjout where Ksid in (select Ksid from TableName GROUP by KSID have COUNT (KSID) >1) and rowID not in (select min (ROWID) from TableName GROUP by KSID have COUNT (KSID) >1)

Oracle removes duplicate record statements