sql - 如何从第一个重复项开始删除具有相同 ID 的其余行？

Question

我的表有以下结构DataTable：每一列都是 int 数据类型，RowID是一个标识列和主键。LinkID是一个外键，链接到另一个表的行。

RowID   LinkID   Order  Data    DataSpecifier
1       120      1      1       1
2       120      2      1       3
3       120      3      1       10
4       120      4      1       13
5       120      5      1       10
6       120      6      1       13
7       371      1      6       2
8       371      2      3       5
9       371      3      8       1
10      371      4      10      1
11      371      5      7       2
12      371      6      3       3
13      371      7      7       2
14      371      8      17      4
.................................
.................................

我正在尝试执行一个查询，该查询LinkID以下列方式更改每个批次：

• 以相同的方式获取每一行LinkID（例如，第一批是这里的前 6 行）
• 按Order列排序
• 将Data和DataSpecifier列视为一个比较单元（可以将它们视为一列，称为dataunit）：
  • 从 1 开始保留尽可能多的行，直到出现Order重复dataunit
  • 删除从第一个副本开始的每一行LinkID

所以对于LinkID 120：

• 对批次进行排序（已在此处排序，但仍应这样做）
• 从顶部开始（所以Order=1在这里），只要你没有看到重复就可以了。
• 停在第一个重复Order = 5（dataunit 1 10已经看到）。
• 删除所有具有LinkID=120 AND Order>=5

LinkID 371在对（以及表中的每个其他人）进行类似处理之后LinkID，处理后的表将如下所示：

RowID   LinkID   Order  Data    DataSpecifier
1       120      1      1       1
2       120      2      1       3
3       120      3      1       10
4       120      4      1       13
7       371      1      6       2
8       371      2      3       5
9       371      3      8       1
10      371      4      10      1
11      371      5      7       2
12      371      6      3       3
.................................
.................................

我已经完成了很多 SQL 查询，但从来没有这么复杂的事情。我知道我需要使用类似这样的查询：

DELETE FROM DataTable  
WHERE RowID IN (SELECT RowID
                FROM DataTable
                WHERE -- ?
                GROUP BY LinkID
                HAVING COUNT(*) > 1 -- ?
                ORDER BY [Order]);

但我似乎无法解决这个问题并正确查询。我最好在纯 SQL 中使用一个可执行（和可重用）查询来执行此操作。

标签： sqlsql-servertsqlduplicatessql-delete

Answer 1

我们可以在这里尝试使用 CTE 以使事情变得更容易：

WITH cte AS (
    SELECT *,
        COUNT(*) OVER (PARTITION BY LinkID, Data, DataSpecifier ORDER BY [Order]) - 1 cnt
    FROM DataTable
),
cte2 AS (
    SELECT *,
        SUM(cnt) OVER (PARTITION BY LinkID ORDER BY [Order]) num
    FROM cte
)

DELETE
FROM cte
WHERE num > 0;

这里的逻辑是COUNT用作分析函数来识别重复记录。我们使用LinkIDwithData和的分区DataSpecifier。然后将任何Order值大于或等于第一个非零计数记录的记录作为删除目标。

这是一个演示，表明 CTE 的逻辑是正确的：

演示