按要求写SQL语句，需支持MySQL，求大腿

Question

表persons:
name  |  phone
--------------------
hello   |  123
hello   |  123
hello   |  456
hello   |  789
kitty    |  234
check |  777
check |  777
--------------------

现要求写出SQL语句，要求查询：
1. 只拥有一个电话的人
2. 去除重复的记录

PS：语句要求支持MySQL

预期结果：
name  |  phone
--------------------
kitty    |  234
check |  777
--------------------

Answer 1

来个最常规的吧, 先去重, 再做分组.

select name, phone from 
    (select distinct name, phone from persons) T
group by name
having count(*) = 1;

---

看了楼主的答案, 受启发了, 这么写也可以的:

select name, phone from persons
group by name
having count(distinct phone) = 1;

Answer 2

select distinct a.name, b.phone 
    from (
         select name , count(distinct phone) 
          from persons group by name                     
         having count(distinct phone) = 1) a 
    inner join persons b on( a.name = b.name)

但是这个我没有实际测试，你可以修改下

把本意的写法，纠正了过来， 感谢楼主的提醒。当然 @brayden 的写法好很多，推荐他的，学习了

Answer 3

看了brayden的语句，发现自己功力严重不够=.=，但是本着不删回答的原则，还是把我的语句保留给大家当反面教材吧

---

自己琢磨写出来了，就是不知道效率如何：

select b.name,a.phone from persons a 
    inner join
        (select name,count(distinct phone) pcount 
            from persons
            group by name
            having cn=1) b
where b.name=a.name
group by a.name

Answer 4

好久没有写sql了，我刚才也试着写了一下，感觉应该不是太好，虽然出了结果：

SELECT name, phone from (SELECT DISTINCT(phone), name FROMsegment) A GROUP BY name HAVING COUNT(name) = 1