MySQL的使用具有和计数

Question

正在试图删除删除与子查询的几个条目使用以下查询：

首先，我找到这些条目，我想用这个查询删除：

SELECT guid FROM account GROUP BY guid,type HAVING count(type) > 1); 

然后我这个查询添加到DELETE语句：

DELETE FROM account WHERE guid IN (SELECT guid FROM account GROUP BY guid,type HAVING count(type) > 1); 

，但我得到这个错误：

您不能指定更新目标表“帐户” FROM子句

Answer 1

我认为你需要使用临时表以实现您的需求如下：

1. 第一步：创建临时表

   CREATE TEMPORARY TABLE MyTemp 
   SELECT guid FROM account 
   GROUP BY guid,type HAVING count(type) > 1; 
   

2. 使用临时表中的delete语句

   DELETE FROM account 
   WHERE guid IN (SELECT guid FROM MyTemp); 
   

3. 删除临时表

   DROP TEMPORARY TABLE MyTemp; 
   

编辑：我觉得与* 两个嵌套选项卡解决莱也可以工作：

DELETE FROM account 
    WHERE guid IN 
    (SELECT guid FROM 
     (SELECT guid FROM account 
     GROUP BY guid,type HAVING count(type) > 1) as MyTemp 
    ) 

Answer 2

首先创建视图

create view view_acct as 
SELECT guid FROM account 
GROUP BY guid,type HAVING count(type) > 1; 

使用后视图

DELETE FROM account WHERE guid in (select * from view_acct); 

Answer 3

你的问题就解决了，只是做如下..

DELETE FROM account 

     WHERE guid IN 

    (SELECT * FROM 

     (SELECT guid FROM account 

      GROUP BY guid,type 

      HAVING count(type) > 1) AS a);