tb1
Name Phone
张三 111
李四 222
王五 111
赵钱 333
孙立 333
...
...
如何找出同一个电话号码在不同姓名下都存在的记录,查询出以下结果
Name Phone
张三 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 行受影响)*/