如何找出同一个电话号码在不同姓名下都存在的记录
tb1
NamePhone
张三111
李四222
王五111
赵钱333
孙立333
...
...

如何找出同一个电话号码在不同姓名下都存在的记录,查询出以下结果
NamePhone
张三111
王五111
赵钱333
孙立333


[解决办法]

SQL code

select * from tb t where exists(select 1 from tb where name<>t.name and phone=t.phone)
[解决办法]
SQL code
create table tb1(Name varchar(6), Phone varchar(10))insert into tb1select '张三', '111' union allselect '李四', '222' union allselect '王五', '111' union allselect '赵钱', '333' union allselect '孙立', '333'select a.*from tb1 ainner join(select Phonefrom tb1group by Phone having count(distinct Name)>1) bon a.Phone=b.PhoneName   Phone------ ----------张三     111王五     111赵钱     333孙立     333(4 row(s) affected)
[解决办法]
SQL code
if object_id('tb') is not null   drop table tbgocreate table tb( name varchar(10), phone int)goinsert into tbselect '张三',111 union allselect '李四',222 union allselect '王五',111 union allselect '赵钱',333 union allselect '孙立',333goselect * from tb a where (select count(*) from tb where phone=a.phone)>1go/*name       phone---------- -----------张三         111王五         111赵钱         333孙立         333(4 行受影响)*/