mysql - 删除两个值匹配的行，mysql

Question

我想要除一行之外的所有行都包含相同的数据，问题是数据在多个字段中。

|id | ValA | ValB  |
|1  | abc  | abc   |
|2  | abc  | abc   |
|3  | abc  | def   |

在上面的示例表中，我想删除 ID 为 2 的行，并保留 1 和 3。

我能找到的唯一解决方案是在 PHP 中使用循环，但我有 170 万条记录，所以我不知道要循环遍历所有这些值。

我认为“SQL 方式”将是子查询，但我无法解决它们

谢谢

Answer 1

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,vala CHAR(3) NOT NULL
,valb CHAR(3) NOT NULL
);

INSERT INTO my_table VALUES
(1  ,'abc','abc'),
(2  ,'abc','abc'),
(3  ,'abc','def');

SELECT * FROM my_table;
+----+------+------+
| id | vala | valb |
+----+------+------+
|  1 | abc  | abc  |
|  2 | abc  | abc  |
|  3 | abc  | def  |
+----+------+------+


SELECT * 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.vala = x.vala 
   AND y.valb = x.valb 
   AND y.id < x.id;
+----+------+------+------+------+------+
| id | vala | valb | id   | vala | valb |
+----+------+------+------+------+------+
|  1 | abc  | abc  | NULL | NULL | NULL |
|  2 | abc  | abc  |    1 | abc  | abc  |
|  3 | abc  | def  | NULL | NULL | NULL |
+----+------+------+------+------+------+

SELECT x.* 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.vala = x.vala 
   AND y.valb = x.valb 
   AND y.id < x.id 
 WHERE y.id IS NULL;
+----+------+------+
| id | vala | valb |
+----+------+------+
|  1 | abc  | abc  |
|  3 | abc  | def  |
+----+------+------+

SELECT x.*  
  FROM my_table x 
  JOIN my_table y 
    ON y.vala = x.vala 
   AND y.valb = x.valb 
   AND y.id < x.id;
+----+------+------+
| id | vala | valb | 
+----+------+------+
|  2 | abc  | abc  | 
+----+------+------+

DELETE x 
  FROM my_table x 
  JOIN my_table y 
    ON y.vala = x.vala 
   AND y.valb = x.valb 
   AND y.id < x.id;

SELECT * FROM my_table;
+----+------+------+
| id | vala | valb |
+----+------+------+
|  1 | abc  | abc  |
|  3 | abc  | def  |
+----+------+------+