SQL删除表中的重复项

Question

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

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) 

Answer 1

它可能是更有效地做类似

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) 

Answer 2

我会尝试这样的事：

DELETE transaction 
FROM transaction 
LEFT OUTER JOIN 
    (
     SELECT MIN(id) as id, date, amount, refnumber, parentfolderid 
     FROM transaction 
     GROUP BY date, amount, refnumber, parentfolderid 
    ) as validRows 
ON transaction.id = validRows.id 
WHERE validRows.id IS NULL 

Answer 3

DELETE FROM transaction 
     WHERE ID IN (
       SELECT ID 
       FROM (SELECT ID, 
          ROW_NUMBER() OVER (PARTITION BY date 
                  ,amount 
                  ,refnumber 
                  ,parentfolderid 
               ORDER BY ID) rn 
               FROM transaction) 
       WHERE rn <> 1); 

我会尝试这样