A method declare @max integer, @ id integerdeclare cur_rows cursor local for select primary field, count (*) from table group by the main field having 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_ROWSET ROWCOUNT 0 Method 2 There are two sense recording, one is full repeated record, that is, all fields Repeated records, the second is a repeated record of some of the key fields, such as the Name field duplicate, and other fields are not repeated or repeated. 1. For the first repetition, it is easier to solve, using the Select Distinct * from TableName, you can get the result set without duplicate records. If the table needs to delete repetitive records (repeated record reserved 1), you can delete Select Distinct * Into #TMP from Tablename DROP TABLENAME SELECT * INTO TABLENAME from #tmp DROP TABLE #TMP is the reason for this repetition. The design is not yaw, and the unique index will be addressed. 2, this type of repetition problem typically requires retention of the first record in the repeated record, the operation method is assumed to have a repeated field is Name, address, requiring the unique result of these two fields SelectiTITY (Int, 1, 1) as autoID, * into #Tmp from tableName select min (autoID) as autoID into # Tmp2 from #Tmp group by Name, autoID select * from #Tmp where autoID in (select autoID from # tmp2) to obtain the last select Name, Address does not repeat the result set (but there is a more autoid field, you can write this column in the SELECT clause when you are actually written)
