使用SELECT DISTINCT name FROM table得出的是没有重复的记录,我现在想查询出表中没有重复的那些记录,应该怎样查询呢?
比如表中有
name tel
chen 5454345
li 4566554
sun 3647636
chen 3847844我现在想要得到的结果是:
li 4566554
sun 3647636
比如表中有
name tel
chen 5454345
li 4566554
sun 3647636
chen 3847844我现在想要得到的结果是:
li 4566554
sun 3647636
drop table if exists tablename;
create table tablename
select 'chen' name,'5454345' tel union all
select 'li','4566554' union all
select 'sun','3647636' union all
select 'chen','3847844';
select t.*
from tablename t,
(
select t1.name
from tablename t1
group by t1.name having count(1)=1
) t2
where t.name=t2.name;-- 结果如下:
'li', '4566554'
'sun', '3647636'
狼头这个不错,这个最简洁了,赞一个先。select * from tablename group by name having count(1)=1;
就使用group by having..
select a.* from table a
inner join (select name from table group by name having count(*)=1) b
on a.name=b.name
mysql里面默认的标准sql是inner join,不过大家在写sql的时候,为了方便,都省略了。