create table 客户表 (CustomerID int, Fullname varchar(20))insert into 客户表 select 1, '河北省分校' union all select 2, '河南省分校' union all select 3, '河北省分校' union all select 4, '陕西省分校' union all select 5, '江西省分校' union all select 6, '海南省分校' union all select 7, '安徽省分校'create table 联系人表 (ContactID int, CustomerID int, Name varchar(10), Telephone varchar(20))insert into 联系人表 select 1, 2, '张三', '10000000' union all select 2, 2, '李四', '10000000' union all select 3, 4, '王五', '10000001' union all select 4, 5, '赵六', '10000001' union all select 5, 6, '田七', '10000002' union all select 6, 6, '小明', '10000002' union all select 7, 7, '小黄', '10000003'select * from 客户表 where Fullname IN(select Fullname from 客户表 group by Fullname having count(*)>1) or CustomerID IN(select CustomerID from 联系人表 where TelePhone IN(select TelePhone from (select distinct CustomerID,Telephone from 联系人表)t group by TelePhone having count(*)>1))/* CustomerID Fullname 1 河北省分校 3 河北省分校 4 陕西省分校 5 江西省分校 */
或者是: ;with cte as ( select TelePhone from (select distinct CustomerID,Telephone from 联系人表)t group by TelePhone having count(*)>1 ) select * from 客户表 t where Fullname IN(select Fullname from 客户表 group by Fullname having count(*)>1) or exists(select 1 from 联系人表 a where a.CustomerID=t.CustomerID and a.TelePhone IN(select TelePhone from cte))/* CustomerID Fullname 1 河北省分校 3 河北省分校 4 陕西省分校 5 江西省分校 */
select a.* from 客户表 a where exists(select 1 from 客户表 b where b.CustomerID<>a.CustomerID and b.Fullname=a.Fullname) union select a.* from 客户表 a,联系人表 c where a.CustomerID=c.CustomerID and exists(select 1 from 联系人表 b where b.CustomerID<>a.CustomerID and b.Telephone=c.Telephone)/* CustomerID Fullname ----------- -------------------- 1 河北省分校 3 河北省分校 4 陕西省分校 5 江西省分校(4 row(s) affected) */
(CustomerID int, Fullname varchar(20))insert into 客户表
select 1, '河北省分校' union all
select 2, '河南省分校' union all
select 3, '河北省分校' union all
select 4, '陕西省分校' union all
select 5, '江西省分校' union all
select 6, '海南省分校' union all
select 7, '安徽省分校'create table 联系人表
(ContactID int, CustomerID int, Name varchar(10), Telephone varchar(20))insert into 联系人表
select 1, 2, '张三', '10000000' union all
select 2, 2, '李四', '10000000' union all
select 3, 4, '王五', '10000001' union all
select 4, 5, '赵六', '10000001' union all
select 5, 6, '田七', '10000002' union all
select 6, 6, '小明', '10000002' union all
select 7, 7, '小黄', '10000003'select *
from 客户表
where Fullname IN(select Fullname from 客户表 group by Fullname having count(*)>1)
or CustomerID IN(select CustomerID from 联系人表 where TelePhone IN(select TelePhone from (select distinct CustomerID,Telephone from 联系人表)t group by TelePhone having count(*)>1))/*
CustomerID Fullname
1 河北省分校
3 河北省分校
4 陕西省分校
5 江西省分校
*/
;with cte as
(
select TelePhone
from (select distinct CustomerID,Telephone from 联系人表)t
group by TelePhone
having count(*)>1
)
select *
from 客户表 t
where Fullname IN(select Fullname from 客户表 group by Fullname having count(*)>1)
or exists(select 1 from 联系人表 a where a.CustomerID=t.CustomerID and a.TelePhone IN(select TelePhone from cte))/*
CustomerID Fullname
1 河北省分校
3 河北省分校
4 陕西省分校
5 江西省分校
*/
select a.* from 客户表 a
where exists(select 1 from 客户表 b
where b.CustomerID<>a.CustomerID and b.Fullname=a.Fullname)
union
select a.* from 客户表 a,联系人表 c
where a.CustomerID=c.CustomerID
and exists(select 1 from 联系人表 b
where b.CustomerID<>a.CustomerID and b.Telephone=c.Telephone)/*
CustomerID Fullname
----------- --------------------
1 河北省分校
3 河北省分校
4 陕西省分校
5 江西省分校(4 row(s) affected)
*/