SQL删除表中的重复项 [英] SQL to delete the duplicates in a table

问题描述

我有一个重复的表事务.我想保留具有最小id的记录，并基于四个字段DATE，AMOUNT，REFNUMBER，PARENTFOLDERID删除所有重复项.我写了这个查询，但是我不确定这是否可以有效地编写.您认为有更好的方法吗?我问是因为我担心运行时间.

I have a table transaction which has duplicates. i want to keep the record that had minimum id and delete all the duplicates based on four fields DATE, AMOUNT, REFNUMBER, PARENTFOLDERID. I wrote this query but i am not sure if this can be written in an efficient way. Do you think there is a better way? I am asking because i am worried about the run time.

DELETE FROM TRANSACTION
WHERE ID IN 
(SELECT FIT2.ID
FROM
(SELECT MIN(ID) AS ID, FIT.DATE, FIT.AMOUNT, FIT.REFNUMBER, FIT.PARENTFOLDERID
FROM EWORK.TRANSACTION FIT
GROUP BY FIT.DATE, FIT.AMOUNT , FIT.REFNUMBER, FIT.PARENTFOLDERID
HAVING COUNT(1)>1 and FIT.AMOUNT >0) FIT1,
EWORK.TRANSACTION FIT2

WHERE FIT1.DATE=FIT2.DATE AND
FIT1.AMOUNT=FIT2.AMOUNT AND
FIT1.REFNUMBER=FIT2.REFNUMBER AND 
FIT1.PARENTFOLDERID=FIT2.PARENTFOLDERID AND 
FIT1.ID<>FIT2.ID)

推荐答案

做类似的事情可能会更有效

It would probably be more efficient to do something like

DELETE FROM transaction t1
 WHERE EXISTS( SELECT 1
                 FROM transaction t2
                WHERE t1.date = t2.date
                  AND t1.refnumber = t2.refnumber
                  AND t1.parentFolderId = t2.parentFolderId
                  AND t2.id > t1.id )

这篇关于SQL删除表中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！