mysql - 删除同一个表上 JOIN 中多列的重复项

Question

我正在尝试从连接的同一个表中进行删除，如下所示:

DELETE FROM `sp10_seo_url` AS sp1 JOIN
( 
        SELECT seo_url_pk, COUNT(*) AS maxc 
        FROM `sp10_seo_url` 
        GROUP BY seo_url_entity_type, seo_url_entity_id, seo_url_language_fk 
        HAVING maxc  > 1 
) AS sp2

ON sp1.seo_url_pk = sp2.seo_url_pk

但是我收到了 mysql 错误

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS sp1 JOIN ( SELECT seo_url_pk, COUNT(*) AS maxc FROM `sp10_s' at line 1

而且我根本不确定错误出在哪里。内部查询运行良好并返回预期的结果集。 “ON”键的命名正确(与我们讨论的是同一个表相同)。

我想查询的想法很清楚(干净的不同行的表对于三个“分组依据”列具有相同的值集。还有其他方法可以做到这一点吗？

谢谢!

Answer 1

您可以使用双重间接“欺骗”mysql(如此处所述Deleting a row based on the max value):

delete from `sp10_seo_url`
where seo_url_pk in (
  select seo_url_pk from (
  SELECT seo_url_pk
  FROM `sp10_seo_url` sp1,
  (
    SELECT seo_url_entity_type, seo_url_entity_id, seo_url_language_fk 
    FROM `sp10_seo_url` 
    GROUP BY seo_url_entity_type, seo_url_entity_id, seo_url_language_fk 
    HAVING count(*)  > 1 
  ) sp2
  where sp1.seo_url_entity_type    = sp2.seo_url_entity_type
  and   sp1.seo_url_entity_id      = sp2.seo_url_entity_id
  and   sp1.seo_url_language_fk    = sp2.seo_url_language_fk
  ) t
  );

http://sqlfiddle.com/#!2/899ff5/1