删除重复值SQL

Question

SELECT 
    * 
FROM 
    Registrations AS R1 
    INNER JOIN Registrations AS R2 
     ON R2.UserItemId = R1.UserItemId 
     AND R2.CourseOfferingId = R1.CourseOfferingId 
     AND R2.Id != R1.Id 
WHERE 
    R2.Id > R1.Id 

我应该如何删除重复值？

感谢

Answer 1

我会在你的问题采取了刺。这只是一个猜测，基于您提供的查询。一个完整的问题将描述你的意思是一个重复的行。

delete from Registrations 
where exists (
    select 1 
    from Registrations r2 
    where r2.UserItemId = Registrations.UserItemId 
     and r2.CourseOfferingId = Registrations.CourseOfferingId 
     and r2.Id < Registrations.Id 
); 

显然，你必须具有相同UserItemId和CourseOffereningId但不同Id值多行。我假设你想保留最低的Id并丢弃其他人。

查询中的子查询会为每个行的表，并检查是否有喜欢它，但以较低的编号另一行。如果答案是肯定的，则该行被删除。

Answer 2

你可以尝试这样的查询

;with cte as (
select *, RowN = row_number() over(partition by useritemid, courseofferingid, id order by id) from Registrations) 
delete from cte where Rown > 1