根据特定条件从CTE删除重复项

本文介绍了根据特定条件从CTE删除重复项的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想基于某些条件从CTE中删除重复的条目，即是否有2条记录具有相同的电子邮件地址，我只希望具有refduserID的记录。另一个具有0 refdUserID的重复记录应被删除。

I want to remove duplicate entries from my CTE based on a certain criteria that is if there are 2 records that have the same email address I want to have only record that has a refduserID with it. The other duplicate record which has 0 refdUserID shoud be removed.

推荐答案

将其添加到我的存储过程中，并且可以正常工作。

Added this to my Stored procedure and it worked.

DuplicateSorting AS （

DuplicateSorting AS (

SELECT *，rank（）超过

SELECT *, rank() OVER

（按refdEmail排序或按refduserID desc排序）从加入开始，

(PARTITION BY refdEmail ORDER BY refduserID desc) AS rn FROM JOINED),

RemovedDuplicates AS （ SELECT * FROM DuplicateSorting Wrne = 1 ）

RemovedDuplicates AS ( SELECT * FROM DuplicateSorting WHERE rn = 1 )

SELECT * FROM RemovedDuplicates

SELECT * FROM RemovedDuplicates