SQL: delete查询永远不会结束的问题

Question

这个查询运行良好且快速:

select t.id_facture from
(SELECT distinct id_facture, num_commande 
FROM table) t
group by t.id_facture
having count(*)>1;

但是当我想用它来删除记录

delete from table where id_facture in (
select t.id_facture from
(SELECT distinct id_facture, num_commande 
FROM table) t
group by t.id_facture
having count(*)>1 );

将永远持续，并以错误结束:

Error Code: 2013. Lost connection to MySQL server during query

是否有另一个执行此删除查询?

id_facture  num_commande
1           2
1           3
2           4
2           4
3           5
3           6

我想删除行与id_facture: 1和3，因为他们有不同的num_command相关。id_facture2只有一个相关的num_命令，所以我想保留它。

Answer 1

用JOIN代替IN()…

DELETE
example
FROM
example
INNER JOIN
(
SELECT id_facture
FROM example
GROUP BY id_facture
HAVING COUNT(DISTINCT num_commande) > 1
)
dupes
ON dupes.id_facture = example.id_facture
;

Demo: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=b33532dc29c3f3d889890952d45f26af

Answer 2

DELETE FROM t
WHERE id_facture IN (
SELECT id_facture
FROM (
SELECT t.id_facture
FROM t
GROUP BY t.id_facture
HAVING MIN(t.num_commande) <> MAX(t.num_commande)
) AS x
)

你可以试试上面的#TO_DELETE是你的表