数据库查重

查重语句

select * from team where teamId in (select teamId from team group by teamId having count(teamId)> 1)

select * from team where ID = 2 group by ID having count(*)>1

删除重复记录

delete from team where teamName in (select teamName from team group by teamName having count(teamName) > 1) and teamId not in (select min(teamId) from team group by teamName having count(teamName)>1)

 

查找表中多余的重复记录（多个字段）

select * from team t where (t.teamId,t.teamOrg) in (select teamId,teamOrg from team group by teamId,teamOrg having count(*) > 1)

 

对单个数据去重

select distinct name from members;

--这意味着从members表中取出去重后的name值

 

对多个字段进行去重时

select distinct name,age from members;

--这意味着从members表中过滤掉name + age相同的值

--同样意味着，distinct这个关键字不能在同时查询两个及以上字段的时候，只过滤一个字段，而考虑其他的字段

 

distinct与count()函数相结合的方法

select count(distinct name) from members;

--这意味着从members表中查询过滤后的name的总数

select age,count(distinct name) from members group by age;

 

distinct用在所有字段之前

select distinct name,age from members;

--这种情况是对的

select  name,distinct age from members;

--这种MySQL中会报语法错误

 

如果想对两个字段中的一个去重，而另外一个不去重的替代方法

select name,age from members where name in (select distinct name from members);

--这种子查询可以对两个字段中的一个去重，而另外一个不去重，效率较低

 

Group by

-- 列出 task_id 的所有唯一值（去重后的记录,null也是值）

select task_id from Task group by task_id;

 

-- 任务总数

select count(task_id) task_numfrom (select task_id from Task group by task_id) tmp;