sql 去掉重复保留一个

SET ROWCOUNT 1
WHILE 1=1
BEGIN
DELETE FROM [dbo].[Demo]
WHERE [Id] IN
(
select d1.Id
from [Demo] d1
where exists (select 1 from [Demo] d2 where d1.[EId]=d2.[EId] and d1.[RId]=d2.[RId] group by d2.[EId],d2.[RId] having count(1)>1)
)
IF @@ROWCOUNT=0
BREAK
END
SET ROWCOUNT 0

 

第二种方式：

with m as (
select
*,
row_number() over(partition by id order by id) rn
from [dbo].[temp]
)
delete m where rn > 1
go