--The first case
CREATE TABLE T
(
ID INT IDENTITY (PRIMARY KEY),
A VARCHAR (10),
b VARCHAR (10)
)
--Inserting data
INSERT into T
SELECT ' AA ', ' BB ' UNION all
SELECT ' A1 ', ' BGB ' UNION all
SELECT ' AA ', ' BB ' UNION all
SELECT ' A2 ', ' BB ' UNION all
SELECT ' Aa3 ', ' Beeb ' UNION all
SELECT ' AA ', ' BB ' UNION all
SELECT ' A2 ', ' BB '
SELECT * from T ORDER by a
--the first method of removing duplicates (finding the minimum identification of duplicate items, deleting data that does not contain the minimum identity)
DELETE t WHERE ID not in (
SELECT min (id) ID from the T GROUP by a, b
)
--the second method of removing duplicates (partition sorting, giving the result of sorting each partition to a line number, removing the line number greater than 1)
DELETE T from (
SELECT *,row_number () over (PARTITION by a, b ORDER by ID), ROW from T)
WHERE row>1
--A second case
CREATE TABLE user_student (id decimal (18,0) identity (max), St_name nvarchar (+), class nvarchar (ten), Score decimal (18,2))
Insert into User_student (St_name,class,score)
Values (' Zhang San ', ' Armor ', ' 90 ')
Insert into User_student (St_name,class,score)
Values (' Zhang Si ', ' a ', ' 65 ')
Insert into User_student (St_name,class,score)
VALUES (' Five ', ' a ', ' 88 ')
Insert into User_student (St_name,class,score)
VALUES (' Lie triple ', ' B ', ' 97 ')
Insert into User_student (St_name,class,score)
VALUES (' John Doe ', ' B ', ' 88 ')
Insert into User_student (St_name,class,score)
VALUES (' Lee ', ' B ', ' 78 ')
Insert into User_student (St_name,class,score)
VALUES (' Wang San ', ' C ', ' 86 ')
Insert into User_student (St_name,class,score)
VALUES (' Wangsi ', ' C ', ' 69 ')
Insert into User_student (St_name,class,score)
VALUES (' Harry ', ' C ', ' 90 ')
SELECT * FROM User_student
SELECT St_name,class,score
From (
SELECT row_number () over (PARTITION by CLASS ORDER by score DESC) as num,*
From User_student
) as T
WHERE num<=2-The first two students per class score
DELETE user_student from user_student WHERE ID not in (the SELECT max (ID) as ID from user_student
GROUP by St_name,class,score)--delete the duplicate data that was added later
SQL Server removes duplicate rows