mysql 单表（指定列）重复记录查询

最新推荐文章于 2024-11-29 10:36:48 发布

转载最新推荐文章于 2024-11-29 10:36:48 发布 · 710 阅读

MySQL 专栏收录该内容

9 篇文章

订阅专栏

本文详细介绍了如何使用SQL查询语句在数据库中查找重复的数据，并提供了两种实现方法，包括通过GROUP BY和HAVING子句进行聚合查询，以及内连接方式进一步筛选重复记录。同时，解释了如何输出重复数据的详细信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

表结构及数据：

SELECT * FROM `t1`;
+----+------+-----+
| id | name | add |
+----+------+-----+
| 1 | abc | 123 |
| 2 | abc | 123 |
| 3 | abc | 321 |
| 4 | abc | 123 |
| 5 | xzy | 123 |
| 6 | xzy | 456 |
| 7 | xzy | 456 |
| 8 | xzy | 456 |
| 9 | xzy | 789 |
| 10 | xzy | 987 |
| 11 | xzy | 789 |
| 12 | ijk | 147 |
| 13 | ijk | 147 |
| 14 | ijk | 852 |
| 15 | opq | 852 |
| 16 | opq | 963 |
| 17 | opq | 741 |
| 18 | tpk | 741 |
| 19 | tpk | 963 |
| 20 | tpk | 963 |
| 21 | wer | 546 |
| 22 | wer | 546 |
| 23 | once | 546 |
+----+------+-----+

/* 查找重复数据有哪些种类 */

SELECT
f_name,
f_content
FROM
t1
GROUP BY
f_name,
f_content

HAVING COUNT(1) > 1 /*此句也可写成 having count(f_name || f_content) > 1*/

+------+-----+
| name | add |
+------+-----+
| abc | 123 |
| ijk | 147 |
| tpk | 963 |
| wer | 546 |
| xzy | 456 |
| xzy | 789 |
+------+-----+

/* 查找所有重复数据 */

SELECT
t1.*
FROM
t1,
(
SELECT
f_name,
f_content
FROM
t1
GROUP BY
f_name,
f_content
HAVING COUNT(1) > 1
) AS t2
WHERE
t1.f_name = t2.f_name

AND t1.f_content = t2.f_content;

以上部分还可写成如下形式，更好理解一些。。。

SELECT
t1.id,
t1.f_name,
t1.f_content
FROM
t1
inner join
(
SELECT
f_name,
f_content
FROM
t1
group by
f_name,
f_content
having count(f_name || f_content) > 1
) t2
on t1.f_name = t2.f_name
and t1.f_content = t2.f_content
;

+----+------+-----+
| id | name | add |
+----+------+-----+
| 1 | abc | 123 |
| 2 | abc | 123 |
| 4 | abc | 123 |
| 6 | xzy | 456 |
| 7 | xzy | 456 |
| 8 | xzy | 456 |
| 9 | xzy | 789 |
| 11 | xzy | 789 |
| 12 | ijk | 147 |
| 13 | ijk | 147 |
| 19 | tpk | 963 |
| 20 | tpk | 963 |
| 21 | wer | 546 |
| 22 | wer | 546 |
+----+------+-----+