删除重复的记录Firebird SQL [英] Delete duplicated records Firebird SQL

问题描述

我想删除重复的行，所以我使用了这个select语句来查找所有重复的行.

I want to delete duplicated rows, so I used this select statement to find all duplicated rows.

SELECT * FROM MY_CARD T1
INNER JOIN( SELECT IDCARD, YEAR FROM MYCARD GROUP BY IDCARD, YEAR HAVING COUNT(IDCARD) > 1 ) T2 ON T1.IDCARD = T2.IDCARD AND T1.YEAR=T2.YEAR
WHERE T1.IDRODZ = 5 AND IDCARD=80;

我的结果看起来像这样，但这只是一个简短的例子，有更多重复的记录.

My result looks like that, but this is only short example, there are more duplicated records.

 ID   IDCARD    YEAR IDRODZ
 1     80   2014    5
 2     80   2014    5
 3     80   2014    5
 4     80   2015    5
 5     80   2015    5
 6     80   2015    5

我需要delete语句，它可以帮助我删除重复的值，以便我的表看起来像这样:

I need delete statement, that helps me to delete duplicated values, so that my table could look like that:

 id  IDCARD   YEAR  IDRODZ
  1  80        2014      5
  4  80        2015      5

我该如何实现?

推荐答案

有了唯一的ID，您可以执行以下操作:

As you have a unique ID, you can do something like this:

delete from my_card
where id not in (select min(id)
                 from my_card
                 group by idcard, year);

这将使每个(身份证，年份)组合的ID最小的行保持不变.如果要保留最大的ID，请在子选择中使用max(id).

That will keep the rows with the smallest id for each (idcard, year) combination. If you want to keep the biggest id, use max(id) in the sub-select.

在线示例: http://rextester.com/WXTU26543

这篇关于删除重复的记录Firebird SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！