基于某一列的T-SQL删除重复项

Question

此截图展示了一个表的前8行。对于同一个id（每个id有数千行），基于相同的“updatetime”，我只想保留第一行，删除其余行。例如，在此处，我想删除第3，第5和第8行。两行的所有列可能完全相同（在此处，当updatetime相同时，UpdateMillisec是不同的，但不是必需的）。该截图是查询结果，并且现在没有主键（左侧的最左侧列现在不在表中）。我应该编写什么SQL代码呢？谢谢！

- Ruixin

---

请参考以下链接了解如何在SQL Server中删除重复行：https://dev59.com/2GMl5IYBdhLWcg3wc2tp - The beginner

如果您没有主键并且“UpdateMillisec不同，但不是必需的”，那么您将很难识别要删除的记录。您仍然可以使用类似于top()的东西来限制每次应用delete子句时要删除的记录数量。适当的主键会有很大帮助。 - Carsten Massmann

感谢大家，所有的回答都非常棒！ - Ruixin

Answer 1

有一种简单的方法可以删除重复行。

首先，我们将对记录进行排序并添加行号。
第二步将是删除行号>1的行。

WITH CTE AS
(
SELECT  *
       ,ROW_NUMBER() OVER 
                (PARTITION BY id, updatetime
                     ORDER BY id, updatetime, UpdateMillisec ASC
                     ) AS RowNum
  FROM yourtable

)
SELECT * FROM CTE                    -- for checking the result before deleting
-- DELETE FROM CTE WHERE RowNum > 1  -- uncomment this row for the final DELETE

注意：
要确定哪个记录是第一条，哪个是后续记录（第二条、第三条等），我们必须对数据进行排序。
在删除之前，务必先使用SELECT * FROM CTE检查结果集。

在您的情况下，我已经检查了上述查询的结果集，结果如下：

id  lastprice   updatetime          UpdateMillisec  RowNum
211709  51370   09:30:00.0000000    500             1
211709  51380   09:30:01.0000000    0               1
211709  51370   09:30:01.0000000    500             2
211709  51370   09:30:02.0000000    0               1
211709  51370   09:30:02.0000000    500             2
211709  51370   09:30:03.0000000    0               1
211709  51370   09:30:04.0000000    0               1
211709  51370   09:30:04.0000000    500             2

正如我们所看到的，那些你想要删除的记录，它们的RowNum = 2。因此，最终我们可以将SELECT *更改为DELETE，然后再次执行查询。

- Esteban P.

---

感谢您提供的解决方案。我不知道可以直接从CTE中删除记录！ - Carsten Massmann

不客气。我在stackoverflow上一段时间前找到了这个方便的解决方案，每次需要删除重复项时都会使用它 :) - Esteban P.

感谢大家，所有的回答都非常好！ - Ruixin

Answer 2

按列分区并按time列排序，然后给出行号，并删除不需要的行。

查询

;with cte as(
    select [rn] = row_number() over(
        partition by [id], [lastprice], [updatetime] 
        order by [id], [updatetime], [updateMillisec]
    ), *
    from [your_table_nam]
)
select * from cte -- first select and check whether these are the rows that has to be deleted
where [rn] > 1;

如果没有问题，那么删除具有大于1的[rn]的行。

delete from cte
where [rn] > 1;

- Ullas

Answer 3

我喜欢 @Estban P. 的解决方案。而且我也很想尝试更进一步。结果证明，也可以用这种方式来实现：

DELETE seq FROM (SELECT ROW_NUMBER() 
       OVER(PARTITION BY id, updatetime ORDER BY id, updatetime, updatems ASC) AS RowNum
FROM tbl ) seq where rownum>1;

所以，你甚至不需要使用CTE，在这里查看演示 http://rextester.com/VLZOD12591

- Carsten Massmann