根据特定条件从CTE删除重复项 [英] Removing duplicates from a CTE based on a specific criteria

问题描述

我想基于某些条件从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.

< img src = https://i.stack.imgur.com/M3Agg.png alt =在此处输入图片描述>

推荐答案

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

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

这篇关于根据特定条件从CTE删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！