保留第一个重复记录，并删除其余的记录 [英] Keep first of duplicate records and delete the rest

问题描述

这个问题几乎完成了我的工作，但我的桌子更复杂，没有主键。我也不太明白顶尖的答案，什么是t1和t2的意思。如果这个答案可以适用于我，不妨感谢如果有人解释代码。

This question does pretty much what I want to accomplish, but my table is more complicated and does not have a primary key. I also don't quite understand the top answer, what the t1 and t2 mean. If this answer can be applicable to me, would appreciate if someone explain the code.

我有几个月的表格包含客户端的信息和他们所持有的政策。每个客户端都有唯一的策略ID，但它们可以有多个策略，导致同一个策略ID下的多个记录。重复的记录可以在每个字段中完全不同或完全相同。

I have several months' tables that contain info on clients and the policies they hold. Every client has a unique policy ID, but they can have multiple policies, resulting in multiple records under the same policy ID. The duplicate records can be completely different or exactly the same in every field.

为了我的目的，我只想为每个策略ID保留一个记录。理想情况下，记录保持最高年龄，但如果太复杂，则不需要。注意，可能有多个记录的年龄是该特定政策ID的最大值，那么我们保留哪一个记录就不重要。

For my purposes, I want to keep only one record for each policy ID. Ideally the record kept is the one with the highest Age, but does not need to if it's too complicated. Note there may be more than one record with the age that is the max for that particular Policy ID, then it doesn't matter which one of those we keep.

I不要打算创建一个主键，因为在某些情况下，我将保留两个记录在相同的策略ID下，我将自己修改代码。我也不想创建另一个表，因为我正在使用10多个表。有人建议使用 first（），但我不知道如何将其合并到查询中。

I do not plan on creating a primary key because there are some cases when I will be keeping two records under the same policy ID, which I will make the modification to the code myself. I also don't want to create another table because I am working with 10+ tables. Someone suggested using first(), but I'm not sure how to incorporate it into a query.

请如果您需要任何其他信息，请告知我们，并提前感谢您的帮助。

Please let me know if you need any additional information, and thank you for your help in advance!

=========更新＃1

=========UPDATE #1

好吧，看起来我的问题有点不切实际，所以我将添加一个 autonumber主键。我将如何处理？

Okay, looks like my question was a bit unrealistic, so I will add an autonumber primary key. How will I proceed with that?

推荐答案

这些行上的某些东西：

DELETE Policies.*
FROM Policies
WHERE Policies.ID Not In (
   SELECT TOP 1 id
   FROM   policies p
   WHERE  p.policyid = policies.policyid
   ORDER  BY createdate DESC, id )

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