mysql去重的两种方法详解及实例代码

mysql去重

方法一：

在使用MySQL时，有时需要查询出某个字段不重复的记录，虽然mysql提供有distinct这个关键字来过滤掉多余的重复记录只保留一条，但往往只用它来返回不重复记录的条数，而不是用它来返回不重记录的所有值。其原因是 distinct只能返回它的目标字段，而无法返回其它字段

下面先来看看例子：

   table  id name  1 a  2 b  3 c  4 c  5 b

比如我想用一条语句查询得到name不重复的所有数据，那就必须使用distinct去掉多余的重复记录。

select distinct name from table

 name  a  b  c

select distinct name, id from table

 id name  1 a  2 b  3 c  4 c  5 b

我们再改改查询语句:

select id, distinct name from table

最终好用的语句如下：

select *, count(distinct name) from table group by name

  id name count(distinct name)  1 a 1  2 b 1  3 c 1

哦，对，再顺便说一句，group by 必须放在 order by 和 limit之前，不然会报错。。。。。。。。！OK了

总结语句：select *, count(distinct name) from (select * from table……等嵌套语句) group by name

方法二：

利用group by

SELECT * FROM( select * from customer where user=(   SELECT source_user from customer WHERE user='admin') UNION ALL select * from customer where user=(   select source_user from customer where user=(     SELECT source_user from customer WHERE user='admin')) union ALL select * from customer where user=(   select source_user from customer where user=(     select source_user from customer where user=(       SELECT source_user from customer WHERE user='admin'))) UNION ALL select * from customer where source_user=(/*我的上线的上线的user*/   select user from customer where user=(     select source_user from customer where user=(       SELECT source_user from customer WHERE user='admin'))) union all select * from customer where source_user=(/*我的上线的上线的上线user*/   select user from customer where user=(   select source_user from customer where user=(     select source_user from customer where user=(       SELECT source_user from customer WHERE user='admin'))))) as alias group by user;

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

更多相关文章

随机推荐