mysql - 如何删除 MySQL 表中的 "extra"空行？

Question

包含五个相关字段的表格；

ID (Autoincrement,unique, etc)
ID_customer - ties in each row to another table with customers, not unique, not NULL
value1 - can be 0 and NULL (NULL is not 0)
value2 - can be 0 and NULL (NULL is not 0)
type - can be 1 or 0

我想遍历表格并找到客户有多行的每种情况，如果有多行具有相同的类型值，则删除其值列中只有 0 的任何此类行。例如；

 1 1 40 8 1
 2 1  0 0 1  <--
 3 1 30 5 0
 4 1 45 8 0

在这种情况下，命令需要删除指定的行，但保留其他行。不太难。这样的事情可能会起作用(重复 type = 0):

DELETE FROM table
WHERE value1 = 0 AND value2 = 0 AND type = 1
   AND ID_customer IN
       (SELECT ID_customer
        FROM table
        WHERE type = 1 GROUP BY ID_customer HAVING count(*) > 1)

问题是可能存在这样的情况:

 5 2 0 0 1
 6 2 0 0 1 <--
 7 2 0 0 0
 8 2 0 0 0 <--

在这种情况下，我想删除两行，但不是所有行。有什么方法可以删除重复行，但前提是值列为 0？这行得通吗(同样，对 type = 0 重复)，还是我遗漏了什么？

DELETE
FROM TABLE
WHERE value1 = 0 AND value2 = 0 AND TYPE = 1
  AND ID NOT IN
    (SELECT ID
     FROM TABLE
     WHERE value1 = 0 AND value2 = 0 AND TYPE = 1
     GROUP BY ID_customer)

编辑:我研究了其他重复删除问题，但我找不到一个以我的表需要的方式定义子查询的问题(在我想保留一些重复项的地方)。

Answer 1

那么对于第一个问题:

您对第一个问题的查询导致错误(您不能在 FROM 子句中指定要更新的目标表“table1”

但是，如果您使用派生表(嵌套子查询)，它工作得很好:

DELETE
FROM table1
WHERE value1 = 0
    AND value2 = 0
    AND TYPE = 1
    AND ID_customer IN
        (SELECT ID_customer
         FROM
             (SELECT ID_customer
              FROM table1
              WHERE TYPE = 1
              GROUP BY ID_customer
              HAVING count(*) > 0) AS derived)

对于第二个问题，方法非常相似，但也使用 ID 字段:

DELETE
FROM table1
WHERE ID IN
        (SELECT dups.ID
         FROM
             (SELECT t1.ID
              FROM table1 t1
              JOIN table1 t2 ON t1.ID_Customer = t2.ID_Customer
              AND t1.value1 = t2.value1
              AND t1.value2 = t2.value2
              AND t1.type = t2.type
              AND t1.ID > t2.ID
              WHERE t1.value1 = 0
                  AND t2.value2 =0 ) AS dups);

希望这对您有所帮助。