MYSQL 查询重复数据

1 查询 people_no 重复的记录

SELECT
	*
FROM
	people
WHERE
	people_no IN (
		SELECT
			people_no
		FROM
			people
		GROUP BY
			people_no
		HAVING
			count(people_no) > 1
	);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2 查询 people_no 重复的记录 ，排除最小 id，如果删除改为delete from

SELECT
	*
FROM
	people
WHERE
	people_no IN (
		SELECT
			people_no
		FROM
			people
		GROUP BY
			people_no
		HAVING
			count(people_no) > 1
	)
AND id NOT IN (
	SELECT
		min(id)
	FROM
		people
	GROUP BY
		people_no
	HAVING
		count(people_no) > 1
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

3 查询 people_no、people_name 重复的记录

SELECT
	*
FROM
	people a
WHERE
	(a.people_no, a.people_name) IN (
		SELECT
			people_no,
			people_name
		FROM
			people
		GROUP BY
			people_no,
			people_name
		HAVING
			count(*) > 1
	);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

4 查询 people_no、people_name 重复的记录，排除最小 id

SELECT
	*
FROM
	people a
WHERE
	(a.people_no, a.people_name) IN (
		SELECT
			people_no,
			people_name
		FROM
			people
		GROUP BY
			people_no,
			people_name
		HAVING
			count(*) > 1
	)
AND a.id NOT IN (
	SELECT
		min(id)
	FROM
		people
	GROUP BY
		people_no,
		people_name
	HAVING
		count(*) > 1
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28