- 如何查找反复行
- 如何删除反复行
- 如何查找多列上的反复行
- 谬误的查问语句
- 几种正确的办法
如何查找反复行
第一步是定义什么样的行才是反复行。少数状况下很简略：它们某一列具备雷同的值。本文采纳这一定义，或者你对“反复”的定义比这简单，你须要对 sql 做些批改。本文要用到的数据样本：
create table test(id int not null primary key, day date not null);
insert into test(id, day) values(1, '2006-10-08');
insert into test(id, day) values(2, '2006-10-08');
insert into test(id, day) values(3, '2006-10-09');
select * from test;
+----+------------+
| id | day |
+----+------------+
| 1 | 2006-10-08 |
| 2 | 2006-10-08 |
| 3 | 2006-10-09 |
+----+------------+后面两行在 day 字段具备雷同的值，因而如何我将他们当做反复行，这里有一查问语句能够查找。查问语句应用 GROUP BY 子句把具备雷同字段值的行归为一组，而后计算组的大小。
select day, count(*) from test GROUP BY day;
+------------+----------+
| day | count(*) |
+------------+----------+
| 2006-10-08 | 2 |
| 2006-10-09 | 1 |
+------------+----------+反复行的组大小大于 1。如何心愿只显示反复行，必须应用 HAVING 子句，比方
select day, count(*) from test group by day HAVING count(*) > 1;
+------------+----------+
| day | count(*) |
+------------+----------+
| 2006-10-08 | 2 |
+------------+----------+这是根本的技巧：依据具备雷同值的字段分组，而后知显示大小大于 1 的组。
为什么不能应用 WHERE 子句？因为 WHERE 子句过滤的是分组之前的行，HAVING 子句过滤的是分组之后的行。
如何删除反复行
一个相干的问题是如何删除反复行。一个常见的工作是，反复行只保留一行，其余删除，而后你能够创立适当的索引，避免当前再有反复的行写入数据库。
同样，首先是弄清楚反复行的定义。你要保留的是哪一行呢？第一行，或者某个字段具备最大值的行？本文中，假如要保留的是第一行——id 字段具备最小值的行，意味着你要删除其余的行。
兴许最简略的办法是通过长期表。尤其对于 MYSQL，有些限度是不能在一个查问语句中 select 的同时 update 一个表。简略起见，这里只用到了长期表的办法。
咱们的工作是：删除所有反复行，除了分组中 id 字段具备最小值的行。因而，须要找出大小大于 1 的分组，以及心愿保留的行。你能够应用 MIN()函数。这里的语句是创立长期表，以及查找须要用 DELETE 删除的行。
create temporary table to_delete (day date not null, min_id int not null);
insert into to_delete(day, min_id)
select day, MIN(id) from test group by day having count(*) > 1;
select * from to_delete;
+------------+--------+
| day | min_id |
+------------+--------+
| 2006-10-08 | 1 |
+------------+--------+有了这些数据，你能够开始删除“脏数据”行了。能够有几种办法，各有优劣（详见我的文章 many-to-one problems in SQL），但这里不做具体比拟，只是阐明在反对查问子句的关系数据库中，应用的规范办法。
delete from test
where exists(
select * from to_delete
where to_delete.day = test.day and to_delete.min_id <> test.id
)如何查找多列上的反复行
有人最近问到这样的问题：我的一个表上有两个字段 b 和 c，别离关联到其余两个表的 b 和 c 字段。我想要找出在 b 字段或者 c 字段上具备反复值的行。
咋看很难明确，通过对话后我了解了：他想要对 b 和 c 别离创立 unique 索引。如上所述，查找在某一字段上具备反复值的行很简略，只有用 group 分组，而后计算组的大小。并且查找全副字段反复的行也很简略，只有把所有字段放到 group 子句。但如果是判断 b 字段反复或者 c 字段反复，问题艰难得多。这里提问者用到的样本数据
create table a_b_c(
a int not null primary key auto_increment,
b int,
c int
);
insert into a_b_c(b,c) values (1, 1);
insert into a_b_c(b,c) values (1, 2);
insert into a_b_c(b,c) values (1, 3);
insert into a_b_c(b,c) values (2, 1);
insert into a_b_c(b,c) values (2, 2);
insert into a_b_c(b,c) values (2, 3);
insert into a_b_c(b,c) values (3, 1);
insert into a_b_c(b,c) values (3, 2);
insert into a_b_c(b,c) values (3, 3);当初，你能够轻易看到表外面有一些反复的行，但找不到两行具备雷同的二元组{b, c}。这就是为什么问题会变得艰难了。
谬误的查问语句
如果把两列放在一起分组，你会失去不同的后果，具体看如何分组和计算大小。提问者恰好是困在了这里。有时候查问语句找到一些反复行却漏了其余的。这是他用到了查问
select b, c, count(*) from a_b_c
group by b, c
having count(distinct b > 1)
or count(distinct c > 1);后果返回所有的行，因为 CONT(*)总是 1. 为什么？因为 >1 写在 COUNT()外面。这个谬误很容易被疏忽，事实上等效于
select b, c, count(*) from a_b_c
group by b, c
having count(1)
or count(1);为什么？因为 (b > 1) 是一个布尔值，基本不是你想要的后果。你要的是
select b, c, count(*) from a_b_c
group by b, c
having count(distinct b) > 1
or count(distinct c) > 1;返回空后果。很显然，因为没有反复的{b,c}。这人试了很多其余的 OR 和 AND 的组合，用来分组的是一个字段，计算大小的是另一个字段，像这样
select b, count(*) from a_b_c group by b having count(distinct c) > 1;
+------+----------+
| b | count(*) |
+------+----------+
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
+------+----------+没有一个可能找出全副的反复行。而且最令人丧气的是，对于某些状况，这种语句是无效的，如果谬误地认为就是这么写法，然而对于另外的状况，很可能失去谬误后果。
事实上，单纯用 GROUP BY 是不可行的。为什么？因为当你对某一字段应用 group by 时，就会把另一字段的值扩散到不同的分组里。对这些字段排序能够看到这些成果，正如分组做的那样。首先，对 b 字段排序，看看它是如何分组的
当你对 b 字段排序（分组），雷同值的 c 被分到不同的组，因而不能用 COUNT(DISTINCT c)来计算大小。COUNT()之类的外部函数只作用于同一个分组，对于不同分组的行就无能为力了。相似，如果排序的是 c 字段，雷同值的 b 也会分到不同的组，无论如何是不能达到咱们的目标的。
几种正确的办法
兴许最简略的办法是别离对某个字段查找反复行，而后用 UNION 拼在一起，像这样：
select b as value, count(*) as cnt, 'b' as what_col
from a_b_c group by b having count(*) > 1
union
select c as value, count(*) as cnt, 'c' as what_col
from a_b_c group by c having count(*) > 1;
+-------+-----+----------+
| value | cnt | what_col |
+-------+-----+----------+
| 1 | 3 | b |
| 2 | 3 | b |
| 3 | 3 | b |
| 1 | 3 | c |
| 2 | 3 | c |
| 3 | 3 | c |
+-------+-----+----------+输入 what_col 字段为了提醒反复的是哪个字段。另一个方法是应用嵌套查问：
select a, b, c from a_b_c
where b in (select b from a_b_c group by b having count(*) > 1)
or c in (select c from a_b_c group by c having count(*) > 1);
+----+------+------+
| a | b | c |
+----+------+------+
| 7 | 1 | 1 |
| 8 | 1 | 2 |
| 9 | 1 | 3 |
| 10 | 2 | 1 |
| 11 | 2 | 2 |
| 12 | 2 | 3 |
| 13 | 3 | 1 |
| 14 | 3 | 2 |
| 15 | 3 | 3 |
+----+------+------+这种办法的效率要比应用 UNION 低许多，并且显示每一反复的行，而不是反复的字段值。还有一种办法，将本人跟 group 的嵌套查问后果联表查问。写法比较复杂，但对于简单的数据或者对效率有较高要求的状况，是很有必要的。
select a, a_b_c.b, a_b_c.c
from a_b_c
left outer join (select b from a_b_c group by b having count(*) > 1
) as b on a_b_c.b = b.b
left outer join (select c from a_b_c group by c having count(*) > 1
) as c on a_b_c.c = c.c
where b.b is not null or c.c is not null以上办法可行，我敢肯定还有其余的办法。如果 UNION 能用，我想会是最简略不过的了。