MySQL中查询、删除重复记录的方法大全

MySQL中查询、删除重复记录是使用非常频繁的操作，本文将详细讲解MySQL中查询、删除重复记录的方法大全。

1. 查询重复记录

1.1 利用GROUP BY和HAVING子句

SELECT column_name(s) FROM table_name
GROUP BY column_name(s)
HAVING COUNT(*) > 1;

上述代码中，GROUP BY语句根据字段将重复值合并到一起，并统计其数量，HAVING子句过滤掉非重复行。

示例如下，假设有一张city表如下：

id	name	country_code	district	population
1	Kabul	AFG	Kabol	1780000
2	Qandahar	AFG	Qandahar	237500
3	Herat	AFG	Herat	186800
4	Mazar-e-Sharif	AFG	Balkh	127800
5	Amsterdam	NLD	Noord-Holland	731200

查询city表中country_code重复的记录，查询语句如下：

SELECT country_code, COUNT(*) FROM city
GROUP BY country_code
HAVING COUNT(*) > 1;

结果如下：

country_code	COUNT(*)
AFG	4

1.2 利用子查询

SELECT column_name(s) FROM table_name
WHERE column_name IN (
  SELECT column_name FROM table_name
  GROUP BY column_name HAVING COUNT(*) >1
);

上述代码中，首先用子查询找到重复的记录的字段值，然后在主查询中使用IN语句将其查询出来。

示例如下，假设有一张表student，如下：

s_id	s_name	s_sex	s_course
1	小红	女	语文
2	小明	男	数学
3	小芳	女	英语
4	小兰	女	物理
5	小刚	男	化学
6	小美	女	数学
7	小忠	男	语文

查询出重复的成绩，查询语句如下：

SELECT s_course FROM student
WHERE s_course IN (
  SELECT s_course FROM student
  GROUP BY s_course HAVING COUNT(*) >1
);

结果如下：

s_course
数学
语文

2. 删除重复记录

2.1 利用子查询删除重复记录

DELETE FROM table_name WHERE column_name NOT IN (
  SELECT MAX(column_name) FROM table_name GROUP BY other_column_name
);

上述代码中，子查询查找出所有分组中的最大值，而NOT IN运算符保留最大值，从而删除不需要的记录。

示例如下，假设有一张orders表，如下：

order_id	order_date	customer	employee
1	2019-02-11	123	001
2	2019-02-12	456	002
3	2019-02-13	123	001
4	2019-02-14	789	003
5	2019-02-15	123	001

删除orders表中customer和employee字段出现重复的记录，删除语句如下：

DELETE FROM orders WHERE order_id NOT IN (
  SELECT MAX(order_id) FROM orders GROUP BY customer, employee
);

执行结果，orders表中只剩下order_id为2、4和5的记录，删除了order_id为1和order_id为3的记录。

2.2 利用临时表删除重复记录

CREATE TEMPORARY TABLE temp_table AS
  SELECT * FROM table_name GROUP BY column_name;
TRUNCATE table_name;
INSERT INTO table_name SELECT * FROM temp_table;

上述代码中，首先将查询结果存储到临时表temp_table中，然后将数据从table_name表中清空，并最后将临时表temp_table中的数据重新插入到table_name表中。

示例如下，假设有一张orders表，如下：

order_id	order_date	customer	employee
1	2019-02-11	123	001
2	2019-02-12	456	002
3	2019-02-13	123	001
4	2019-02-14	789	003
5	2019-02-15	123	001

删除orders表中customer和employee字段出现重复的记录，删除语句如下：

CREATE TEMPORARY TABLE temp_table AS
  SELECT * FROM orders GROUP BY customer, employee;
TRUNCATE orders;
INSERT INTO orders SELECT * FROM temp_table;

执行结果，orders表中只剩下order_id为1、2和4的记录，删除了order_id为3和order_id为5的记录。

3. 总结

本文中详细讲解了MySQL中查询、删除重复记录的方法大全，包括利用GROUP BY和HAVING子句、利用子查询和利用临时表删除重复记录，这些方法各有优缺点，读者可根据实际需求进行选择。