此截图展示了一个表的前8行。对于同一个id(每个id有数千行),基于相同的“updatetime”,我只想保留第一行,删除其余行。例如,在此处,我想删除第3,第5和第8行。两行的所有列可能完全相同(在此处,当updatetime相同时,UpdateMillisec是不同的,但不是必需的)。该截图是查询结果,并且现在没有主键(左侧的最左侧列现在不在表中)。我应该编写什么SQL代码呢?谢谢!
有一种简单的方法可以删除重复行。
首先,我们将对记录进行排序并添加行号。
第二步将是删除行号>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
,然后再次执行查询。
按列分区并按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;
delete from cte
where [rn] > 1;
我喜欢 @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
top()
的东西来限制每次应用delete
子句时要删除的记录数量。适当的主键会有很大帮助。 - Carsten Massmann