使用联接从表中删除重复的行

Question

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

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

两个表中已经有数据。

现在的问题

城市表包含一个州内一个城市的多个条目。另一个城市可能也可能没有相同的城市名称

例如：cityone将在city表中使用stateone出现5次，使用statetwo出现2次

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

模式如下

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行

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

有关更多信息，请参见DELETEMySQL语法。