我想基于某些条件从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
更多推荐
根据特定条件从CTE删除重复项
发布评论