使用具有多列的group by进行删除

Question

我想删除此查询返回的所有行。

SELECT col1, col2, col3 FROM myTable GROUP BY col1, col2, col3 HAVING count(*) > 1;

我试过了，但是它给了我一个语法错误。

DELETE FROM myTable WHERE col1, col2, col3 IN ( 
                       SELECT col1, col2, col3 FROM   ( 
                              SELECT   col1, col2, col3 FROM myTable 
                              GROUP BY col1, col2, col3 HAVING count(*) > 1 ) 
                            t );

Answer 1

对表使用内部联接

DELETE t1 FROM myTable t1
        INNER JOIN ( 
                       SELECT col1, col2, col3 FROM   ( 
                              SELECT   col1, col2, col3 FROM myTable 
                              GROUP BY col1, col2, col3 HAVING count(*) > 1 ) 
                            t )
    t2 ON t2.rcol1 = t1.rcol1 AND t2.col2 = t1.col AND t2.col3 = t1.col3;

但是您应该在测试数据库上测试它，因为我认为您的选择不能识别正确的行，最好是有一个唯一的列，它可以识别正确的行，因为这样会删除所有的行