使用连接从表中删除重复行

Question

我有两个表来包含国家的州（state_table）和城市（city_table）

城市表具有state_id以与state_table相关联

这两个表已经有数据。

现在的问题是：

城市表中包含一个州内多个城市的条目。而其他城市可能也有相同的城市名称

例如：在城市表中，cityone将有5个出现次数与stateone和2个出现次数与statetwo

那么，我该如何编写查询以保留每个州的一个城市并删除其余部分？

模式如下

CREATE TABLE IF NOT EXISTS `city_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state_id` int(11) NOT NULL,
  `city` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `state_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state` varchar(15) NOT NULL,
  `country_id` smallint(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

这是示例数据。

id   state_id   city
1   1   city_one
2   1   city_two
3   1   city_one
4   1   city_two
5   2   city_one
6   3   city_three
7   3   city_one
8   3   city_three
9   4   city_four
10  4   city_five

原始表格有152,451行

- zamil

---

请提供相应的样本数据。Translated Text: - Ajith Sasidharan

那么你的意思是你的表中有重复数据，你想要删除所有重复的数据吗？ - Himanshu

你试过我的答案了吗？让我知道它是否有效。 - Saharsh Shah

@SaharshShah 由于表格很大，返回结果需要一些时间。我还在等待... - zamil

@hims056：非常感谢。拥抱 :) 它像魔法一样奏效。 - zamil

Answer 1

如果您想删除具有相同 state_id（重复记录）的重复城市，可以通过按city和state_id分组并使用MIN或MAX函数来实现：

在执行删除查询之前，您的表如下所示：

| ID | STATE_ID |       CITY |
------------------------------
|  1 |        1 |   city_one |
|  2 |        1 |   city_two |
|  3 |        1 |   city_one |
|  4 |        1 |   city_two |
|  5 |        2 |   city_one |
|  6 |        3 | city_three |
|  7 |        3 |   city_one |
|  8 |        3 | city_three |
|  9 |        4 |  city_four |
| 10 |        4 |  city_five |

您可以使用以下查询来删除重复记录：

DELETE city_table 
  FROM city_table
  LEFT JOIN 
  (SELECT MIN(id) AS IDs FROM city_table
   GROUP BY city,state_id
  )A
  ON city_table.ID = A.IDs
  WHERE A.ids IS NULL;

应用上述查询后，您的表格将如下所示：

| ID | STATE_ID |       CITY |
------------------------------
|  1 |        1 |   city_one |
|  2 |        1 |   city_two |
|  5 |        2 |   city_one |
|  6 |        3 | city_three |
|  7 |        3 |   city_one |
|  9 |        4 |  city_four |
| 10 |        4 |  city_five |

查看这个SQLFiddle

更多信息请参见{{link2：MySQL的DELETE语法。}}

- Himanshu

---

你的解决方案不会删除行，如果没有重复吗？也许你的意思是“NOT IN”。 - samuil

@samuil - 我更新了答案。请查看我的另一条评论。 - Himanshu

由于您已经使用了子查询，因此连接和检查空值有点多余。 - samuil

@samuil - 这个查询 运行成功了吗？ - Himanshu

@hims056：非常感谢，拥抱 :) 它像魔法一样奏效 [我把它发错地方了] - zamil

Answer 2

DELETE FROM city_table 
WHERE id NOT IN 
  (SELECT MIN(id) 
   FROM city_table 
   GROUP BY state_id, city)

如果您发现此查询太慢，可以创建临时表，并将子查询的输出存储在其中，然后截断原始表并重新填充其内容。这是一个有点“肮脏”的解决方案，因为您需要设置自增列的值。

- samuil