SQL:选择所有未被另一个查询选择的记录 [英] SQL: select all records not selected by another query

问题描述

我正在寻找一个SQL查询来选择同一表上另一个查询未选择的所有记录. 具体来说，我想选择所有具有特定字段('fieldA')重复项的记录，然后删除除其中一个记录以外的所有记录.

I am looking for an SQL query to select all records not selected by another query on the same table. Specifically I want to select all records which have duplicates of a particular field('fieldA') and then delete all but one of those records.

因此，一条select语句可能类似于以下内容(不起作用！):

So a select statement might be something like the following (which doesn't work!):

select * from table where id not in(select * from table group by fieldA)

如果不可能进行单个查询，那么最有效的解决方案是什么?

If a single query is not possible then what would be the most efficient solution?

推荐答案

具体地说，我想选择所有与特定字段重复的记录("fieldA")，然后删除除其中一个记录以外的所有记录.

Specifically I want to select all records which have duplicates of a particular field('fieldA') and then delete all but one of those records.

在这种情况下，加入它:

delete x 
from myTable x
 join myTable z on x.field = z.field
where x.id > z.id

这篇关于SQL:选择所有未被另一个查询选择的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！