从连接中删除表中的重复行 [英] Remove duplicate rows from table with join
问题描述
我有两个表格包含国家的state（state_table）和city（city_table）
城市表正在使用state_id将其与state_table
相关联这两个表都已经有数据了。
现在问题
城市表包含一个州内的城市的多个条目。另外一个城市可能有也可能没有相同的城市名称，例如：cityone将在城市表中发生5次，其中stateone为2，出现statetwo
那么我如何编写一个查询来为每个州保留一个城市，并删除其余的？
p
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行
如果要删除重复的城市， code> state_id （重复记录），您可以通过将它们分组为 city 和 state_id 并使用 MIN 或 MAX 功能：
在删除查询之前，您的表格看起来像
ID | STATE_ID |城市|
------------------------------
| 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 |
您可以使用以下查询来删除重复记录：
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $，，，，，，，，，，，，，，，，，，，，，，，，，
state_id
）A
ON city_table.ID = A.IDs
WHERE A.ids IS NULL;
应用上述查询后，您的表格将如下所示：
| ID | STATE_ID |城市|
------------------------------
| 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
有关详细信息，请参阅 DELETE MySQL的语法
I have two table to contain state (state_table) and city (city_table) of countries
The city table is having state_id to relate it with state_table
Both the tables are already having data in it.
Now the problem
City table contains multiple entries of a city within one state. And another cities may or may not have the same city name as well
e.g.: cityone will have 5 occurrence in the city table with stateone and 2 occurrence with statetwo
So how will I write a query to keep one city for each state and delete the rest?
Schema follows
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 ;
This is the sample data
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
Original table has 152,451 rows
If you want to remove duplicate city with same state_id (duplicate records), you can do that by grouping them by city and state_id and using MIN or MAX function:
Before delete query your table was looking like
| 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 |
You can use the following query to remove duplicate records:
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;
After applying the above query your table will look like:
| 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 |
See this SQLFiddle
For more see DELETE Syntax of MySQL.
这篇关于从连接中删除表中的重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
