删除两列匹配的地方 [英] Delete where both columns match

问题描述

我有两张桌子

Person (contain oldest date (farthest in future) when person moved OR WILL moved to another address)

ID                OLDESTADDRESSMOVEDATE

1                     20161117
2                     20171211
3                     20160101


Address           

PersonID             AddressMOVETODATE
 1                       20161117
 1                       20161111
 1                       20141018
 2                       20171211
 2                       20151210

现在我想编写一个 DELETE 查询，它将从 PERSON 中的每个人的 ADDRESS THE ROWS 中删除，其中 AddressMOVETODATE 比 OLDESTADDRESSMOVEDATE 列中的那个要早

Now I want to write a DELETE query which will DELETE FROM ADDRESS THE ROWS for EACH person in PERSON, where AddressMOVETODATE is PREVIOUS THAN the one in OLDESTADDRESSMOVEDATE colum

例如

如果我运行该查询，则 ADDRESS 表中的剩余行应该是

If I run that query the remaining rows in ADDRESS table should be

PersonID             AddressMOVETODATE
1                       20161117
2                       20171211

我试过了

DELETE FROM ADDRESS WHERE PERSONID = PERSON.ID 和 AddressMOVETODATE <;Person.OLDESTADDRESSMOVEDATE

但它不起作用.此外，我们将如何确保我们只输入 Person 表的 CORRESPONDING 列，例如我们不想把

but it is not working. Also, how we will make sure we enter only CORRESPONDING columns of Person table e.g. we do not want to mess up by putting

Person ID = 1 and Person.OLDESTADDRESSMOVEDATE = 20171211 (of Person 2)

请帮忙.

谢谢，

艾登

推荐答案

可以使用row_number、cte和delete查询

You can query using row_number, cte and delete

;WITH cte AS
(
    SELECT *, RowN = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY AddressMoveDate DESC) FROM Address
)
DELETE FROM cte WHERE RowN > 1

这篇关于删除两列匹配的地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！