During the use of the database, the repetitive data is sometimes encountered during the use of the database, and the duplicate data has caused the database part settings that cannot be set correctly ...
method one
Declare @max integer, @ ID integerDeclare cur_rows cursoor local for select
Primary field
Count (*) from
Table Name
GROUP BY
Primary field
Having count (*)> 1Open cur_rowsfetch cur_rows @ ID, @maxwhile @@ fetch_status = 0beginselect @max = @max -1set rowcount @maxdelete from
Table Name
WHERE
Primary field
@IDFETCH CUR_ROWS INTO @ ID, @ MaxendClose Cur_rowsset Rowcount 0
Method Two
There are two sense recording, one is a complete repetition record, that is, all fields are repeated records, and the other is a repeated record of some key fields, such as
Name
Fields are repeated, while other fields are not repeated or negligible.
1
For the first repetition, it is easier to solve, use
Select Distinct * from Tablename
You can get the result set without repeated recording.
If the table needs to delete repetitive records (repeated record reserved
1
Article), can be deleted as follows
Select Distinct * Into #tmp from Tablename Drop Table Tablename Select * Into Tablename from #tmp Drop Table #TMP
This repetition is that the design is not yaw in the case, and the unique index will be resolved.
2
Such repetition problems typically require the first record in the repeated record, the operation method is as follows
Suppose there is a repeated field
Name, Address
, Require the unique result set of these two fields
select identity (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 )
the last one
SELECT
Be obtained
Name
,
Address
Not repeated result set (but there is one
AutoID
Field, you can write it when you write
SELECT
省 中 中 中 列))
