删除非唯一行

Question

我有一个表，它有一个唯一的非聚集索引，其中4个列在此索引中列出。 我想要更新表中大量的行。 如果我这样做，它们将不再是唯一的，因此由于索引，更新会失败。

我想禁用索引，然后删除最旧的重复行。 这是我的查询：

SELECT t.itemid, t.fieldid, t.version, updated
FROM dbo.VersionedFields w
inner JOIN
(
    SELECT itemid, fieldid, version, COUNT(*) AS QTY
    FROM dbo.VersionedFields
    GROUP BY itemid, fieldid, version
    HAVING COUNT(*) > 1
) t 
on w.itemid = t.itemid and w.fieldid = t.fieldid and w.version = t.version

内连接中的选择返回了我们想要删除的记录的正确数量，但将它们分组后实际上有两倍的数量。

连接后显示了所有记录，但我只想删除最旧的记录？

如何做到这一点？

- Luke Wilkinson

Answer 1

如果你说的是SQL(结构化查询语言)，但实际上你指的是SQL Server(微软关系数据库系统)，并且你正在使用SQL Server 2005或更新版本，你可以使用CTE (公共表达式)来实现这个目的。

使用CTE，你可以按一些标准对数据进行分区-例如你的ItemId(或一组列)-并让SQL Server为每个分区中的所有行从1开始编号，按另一些标准排序-通常是version(或某些其他列)。

因此，尝试像这样做：

;WITH PartitionedData AS
(
    SELECT 
       itemid, fieldid, version, 
       ROW_NUMBER() OVER(PARTITION BY ItemId ORDER BY version DESC) AS 'RowNum'
    FROM dbo.VersionedFields
)
DELETE FROM PartitionedData
WHERE RowNum > 1

基本上，你是按照某些标准将数据分区，并对每个新的分区从1开始编号，按照其他标准排序（例如日期或版本）。

因此，对于每个数据“分区”，“最新”的条目具有RowNum = 1，而属于同一分区（通过具有相同的分区值来判断）的任何其他条目都将具有从2到该分区中存在的行数的连续编号值。

如果您只想保留最新的条目，请删除任何RowNum大于1的内容，然后完成！

- marc_s

Answer 2

在SQL Server 2005及以上版本中：

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY itemid, fieldid, version ORDER BY updated DESC) AS rn
        FROM    versionedFields
        )
DELETE
FROM    q
WHERE   rn > 1

- Quassnoi

Answer 3

你可能需要查看这个Stack Overflow答案（删除重复行的早期版本）。

本质上，该技术使用分组（或可选地，窗口化）来查找组中最小的id值以便删除它。更准确的做法是删除值不等于max（行标识符）的行。

所以：

1. 删除唯一索引
2. 加载数据
3. 使用分组机制删除数据（最好在事务中，以便在出现错误时可以回滚），然后提交
4. 重新创建索引。

请注意，在大表上重新创建索引可能需要很长时间。

- rorycl

---

marc_s的解决方案是一种优雅的方法。 - rorycl

Answer 4

尝试类似这样的内容：

DELETE FROM dbo.VersionedFields w WHERE w.version < (SELECT MAX(version) FROM dbo.VersionedFields)

当然，您需要将MAX（version）限制为仅包括您想要删除的字段的版本。

- Malfist