艺海无涯,学无止境。欢迎一起探讨技术和人生,了解我

SQL删除重复数据,保留一条数据

DELETE  FROM #table
WHERE   EXISTS ( SELECT node ,
                        mailNo
                 FROM   #table b
                 WHERE  #table.node = b.node
                        AND #table.mailNo = b.mailNo
                 GROUP BY node ,
                        mailNo
                 HAVING COUNT(1) > 1 )
        AND id NOT IN ( SELECT    MIN(a.id)
                              FROM      #table a
                              WHERE     #table.node = a.node
                                        AND #table.mailNo = a.mailNo
                              GROUP BY  node ,
                                        mailNo
                              HAVING    COUNT(1) > 1 );