When writing SQL statements, you often have the following requirement: there are multiple duplicate data records in a Table, how to effectively extract non-duplicate data, or delete duplicate data, or retrieve the first data repeat of a column.
When writing SQL statements, you often have the following requirement: there are multiple duplicate data records in a Table, how to effectively extract non-duplicate data, or delete duplicate data, or retrieve the first data repeat of a column.
The new functions NOW_NUMBER and CTE of SQL Server 2005 can be well implemented.
Example:
Create test data:
The Code is as follows:
Create table Dup1
(
Col1 int null,
Col2 varchar (20) null
)
Insert into Dup1 values
(1, 'aaa '),
(2, 'aaa '),
(2, 'aaa '),
(2, 'aaa '),
(3, 'bbb '),
(3, 'bbb '),
(4, 'ccc '),
(4, 'ddd '),
(5, 'eee ')
Select * from Dup1
Duplicate data can be viewed as follows:
The Code is as follows:
SELECT Col1, Col2, COUNT (*) AS DupCountFROM Dup1GROUP BY Col1, Col2HAVING COUNT (*)> 1
Next we will introduce how to delete duplicate data:
1. NOW_NUMBER: A good RANKING function (NOW_NUMBER, RANK, DENSE_RANK, NTILE) is added to SQL Server 2005. NOW_NUMBER () OVER (PARTITION GY) is the most direct and convenient method, you cannot modify tables or generate extra columns.
First, a column number is assigned, Which is sorted by Col1 and Col2.
The Code is as follows:
SELECT Col1, Col2, ROW_NUMBER () OVER (partition by Col1, Col2 order by Col1) AS rnFROM Dup1
The sequence number is as follows:
Obviously, duplicate columns are grouped and sorted by group. You only need to delete the sorting sequence number> 1.
The Code is as follows:
-- Use CTE
WITH DupsD
AS (
SELECT Col1, Col2,
ROW_NUMBER () OVER (partition by Col1, Col2 order by Col1) AS rn
FROM Dup1
)
DELETE DupsD
WHERE rn> 1;
-- Or
Delete a from (
SELECT Col1, Col2,
ROW_NUMBER () OVER (partition by Col1, Col2 order by Col1) AS rn
FROM Dup1) a where a. rn> 1
2. Create a table with a unique ID key.
The Code is as follows:
Alter table dbo. Dup1
ADD
PK INT IDENTITY
NOT NULL
CONSTRAINT PK_Dup1 primary key;
SELECT *
FROM Dup1;
Delete the records that are the same as those of Col1, Col2 and larger than Dup1.PK, that is, keep the records with the minimum PK value.
The Code is as follows:
DELETE Dup1
Where exists (SELECT *
FROM Dup1 AS D1
WHERE D1.Col1 = Dup1.Col1
AND D1.Col2 = Dup1.Col2
AND D1.PK> Dup1.PK );
3. select distant into. This method transfers non-duplicated result sets to the new table by using a new table.
The Code is as follows:
SELECT distinct Col1, Col2 INTO NoDupsFROM Dup1; select * from NoDups
The first and third methods are recommended. The first method is more common in programming of T-SQL, and the third method is often used in ETL.
