如何删除所有重复项只保留其中一个

问题描述：

我的查询应该删除所有重复的数据：

WITH todelete As (
     select em.*, 
      row_number() over (partition by emp_num, [from_date],[to_date],[ req_ser], [ req_year] 
           order by (select null)) as cnt 
     from empmission em 
) 

DELETE FROM todelete 
WHERE cnt > 2;

其实，这应该留下一式两份。你的where子句应该是'WHERE cnt> 1'。 –

答

首先我建议你不要使用CTE使用临时表为您的解决方案使用下面的链接，找到为什么我是个说这

https://dba.stackexchange.com/questions/13112/whats-the-difference-between-a-cte-and-a-temp-table

现在让我们回到你的解决方案，我已经使用临时表

Select e.emp_num,e.from_date,e.to_date,e.[ req_ser],e.[ req_year],COUNT(1) number_of_duplicates 
into #temp1 from empmission e 
group by emp_num,from_date,to_date,[ req_ser],[ req_year] 

Truncate Table empmission 


Insert into empmission 
Select t.emp_num, 
     t.from_date, 
     t.to_date, 
     t.[ req_ser], 
     t.[ req_year] 
    from #temp1 t 
    Drop Table #temp1  --Droping the temp table 

    Select * from empmission

或者你可以用CTE 去作为

WITH todelete As (
    select em.*, 
     row_number() over (partition by emp_num, [from_date],[to_date],[ req_ser], [ req_year] 
          order by (select null)) as cnt 
    from empmission em 
) 
    DELETE FROM todelete 
WHERE cnt > 1;

答

正如评论通过Zohar的规定，您的查询将保持two重复行不one ..To只保留一个行更改查询像下面..

Check out this question and all its answers几种方式删除像rownumber，group by，自我加入，还优化提示何时使用group by vs rownumber ..

WITH todelete As (
     select em.*, 
      row_number() over (partition by emp_num, [from_date],[to_date],[ req_ser], [ req_year] 
           order by (select null)) as cnt 
     from empmission em 
) 

DELETE FROM todelete 
WHERE cnt > 1;

如何删除所有重复项只保留其中一个

相关推荐