sqlserver如何删除表中除主键外一模一样的数据

--创建测试表
create table test2  
(  
TestId UNIQUEIDENTIFIER not null ,   
AddedOn varchar(50) not null,   
Action varchar(50) not null, 
Item int  not null, 
Parent int  not null, 
Param int  not null, 
BoolParam int  not null, 
Content int  not null, 
Proprties int  not null, 
)  
SELECT * FROM dbo.test2
--新增测试数据
INSERT INTO test2  VALUES (NEWID(),'1','1','1','1','1','1','1','1')
INSERT INTO test2  VALUES (NEWID(),'3','3','3','3','3','3','3','3')

--方法一   将某个字段分区进行排序，然后删除排序号为1以上的
DELETE T FROM (SELECT ROW_NUMBER() OVER(PARTITION BY Action ORDER BY Action) AS ROWNUMBER,*  FROM test2 ) T
WHERE T.ROWNUMBER>1
--方法二   将某个字段分组，查询出条数大于1的最小主键，然后根据主键删除
DELETE t1
FROM test2 t1
WHERE t1.TestId >
    (
        SELECT MIN(t2.TestId)
        FROM test2 t2
        WHERE t1.Action = t2.Action
        GROUP BY Action
        HAVING COUNT(Action) > 1
    );

本文地址：https://blog.csdn.net/q1923408717/article/details/111030431