sql - 如何识别重复记录的唯一标识符？

Question

我在一个表中有重复的记录。我只需要能够识别一个唯一标识符，这样我就可以将其从表中删除。

我知道有重复项的唯一方法是从列 subject 和 description 所以如果至少有 2 个相同的主题和相同的描述，我需要删除一个，留下一个。

所以我能够获得重复记录的列表，但我无法获得能够删除它的唯一标识符。

这就是我为识别重复记录所做的工作。

SELECT 
    p.accountid, p.subject, p.description, count(*) AS total
FROM
    activities AS p 
WHERE     
    (p.StateCode = 1) AND p.createdon >= getdate()-6
GROUP BY 
    p.accountid, p.subject, p.description
HAVING 
    count(*) > 1
ORDER BY 
    p.accountid

有一列 record_id 保存每条记录的唯一标识符。但是，如果我将 record_id 添加到我的 select 语句中，那么我将得不到任何结果，因为不可能有重复的唯一标识符

如何使用 SQL Server 获取 record_id？

注意:record_id 不是整数，它类似于“D32B275B-0B2F-4FF6-8089-00000FDA9E8E”

谢谢

Answer 1

我喜欢 SQL Server 的一个很好的特性是使用 CTE 和 update 和 delete 语句。

您正在寻找重复的记录并且可能希望保留最低或最高的 record_id。您可以获得计数和 ID 以继续使用 CTE 和窗口函数:

with todelete as (
    SELECT p.accountid, p.subject, p.description,
           COUNT(*) over (partition by p.accountid, p.subject, p.description) as total,
           MIN(record_id) over (partition by p.accountid, p.subject, p.description) as IdToKeep
    FROM activities AS p 
    WHERE (p.StateCode = 1) AND p.createdon >= getdate()-6
   )
delete from todelete
    where total > 1 and record_id <> IdToKeep;

最后的 where 子句只是使用逻辑来选择要删除的正确行。

我要补充一点，如果你只想要将被删除的列表，你可以使用类似的查询:

with todelete as (
    SELECT p.accountid, p.subject, p.description,
           COUNT(*) over (partition by p.accountid, p.subject, p.description) as total,
           MIN(record_id) over (partition by p.accountid, p.subject, p.description) as IdToKeep
    FROM activities AS p 
    WHERE (p.StateCode = 1) AND p.createdon >= getdate()-6
   )
select *
from todelete
 where total > 1 and record_id <> IdToKeep;

over 函数表示一个函数被用作窗口函数。这个想法很简单。 Count(*) over 返回在 partition 子句中具有相同字段值的所有记录的计数。它很像聚合函数，只是您获取每一行的值。这类功能非常强大，建议大家多多了解。