日期:2014-05-18 浏览次数:20498 次
select * from tb t where exists(select 1 from tb where name<>t.name and phone=t.phone)
------解决方案--------------------
create table tb1(Name varchar(6), Phone varchar(10)) insert into tb1 select '张三', '111' union all select '李四', '222' union all select '王五', '111' union all select '赵钱', '333' union all select '孙立', '333' select a.* from tb1 a inner join (select Phone from tb1 group by Phone having count(distinct Name)>1) b on a.Phone=b.Phone Name Phone ------ ---------- 张三 111 王五 111 赵钱 333 孙立 333 (4 row(s) affected)
------解决方案--------------------
if object_id('tb') is not null drop table tb go create table tb ( name varchar(10), phone int ) go insert into tb select '张三',111 union all select '李四',222 union all select '王五',111 union all select '赵钱',333 union all select '孙立',333 go select * from tb a where (select count(*) from tb where phone=a.phone)>1 go /* name phone ---------- ----------- 张三 111 王五 111 赵钱 333 孙立 333 (4 行受影响) */