桐乡做网站的公司,视频网站建站费用,小程序平台服务协议,wordpress资源站如何查找重复行
第一步是定义什么样的行才是重复行。多数情况下很简单#xff1a;它们某几列具有相同的值。本例采用这种定义#xff0c;或许你对“重复”的定义得很复杂#xff0c;你需要对sql做些修改。本例要用到的数据样本#xff1a;
create table test(id int not …如何查找重复行
第一步是定义什么样的行才是重复行。多数情况下很简单它们某几列具有相同的值。本例采用这种定义或许你对“重复”的定义得很复杂你需要对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字段具有最小值的行。因此需要找出count字段大于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 |有了这些数据你可以开始删除“脏数据”了。可以有几种方法各有优劣但这里不做详细比较只是说明在支持查询语句的关系数据库中使用的标准方法。
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能行我想会是最简单不过的了。
