请求提供查询以删除重复记录

Question

案例-1

如果所有记录都处于该特定Ref_No的异常状态，则为

删除除第一记录外的所有记录

案例-2

如果某些记录处于异常状态，而对于特定的Ref_No有一条处于匹配状态

删除所有状态异常的记录

下面的查询对于Case-2很好，但不适用于Case-1。请求某人帮助进行查询。

with cte
       as (
       select ROW_NUMBER() over (partition by Ref_No order by Id) RN ,* from 
      Table1
       )
                     
 Delete from cte where Final_status <> 'MATCHED'

​

​

Answer 1

如果“异常”和“匹配”是最终状态的唯一选项，那么您可以在ORDER BY Final_Status DESC中使用row_number。然后添加一个WHERE条件，使其永不删除RN=1

with cte
       as (
       select ROW_NUMBER() over (partition by Ref_No order by Final_Status DESC, Id) RN ,* from 
      Table1
       )
                     
Delete from cte 
where Final_status <> 'MATCHED'
AND RN>1

Answer 2

这样就不需要假设只有两种最终状态：

delete from T
where exists (
    select 1 from T t2
    where t2.Ref_No = T.Ref_No and t2.Id <> T.Id and (
            -- if it's not matched and there's another row that is
            T.Final_status <> 'Matched' and t2.Final_status = 'Matched'
            -- it's not the first row that's non-matched 
        or  T.Id > t2.Id and not 'Matched' in (T.Final_status, t2.Final_status)
    );

您仍然可以使用case表达式将匹配的vs非匹配折叠为两种可排序状态。这个查询更便于移植，并且不依赖于从表表达式中删除的想法。

Answer 3

我们可以通过DESC的订单，使匹配的状态出现在顶部，如果有。如果没有匹配的状态，那么异常状态将出现在顶部。

这样，我们只保留等级1，然后删除重复的。

;WITH CTE AS
(
SELECT ID,Ref_No, ROW_NUMBER() OVER(PARTITION BY Ref_No ORDER BY Final_Status DESC) AS rnk , Final_Status
FROM Table
)
DELETE FROM CTE
WHERE rnk > 1