如何读取存在重复信息的客户
客户表结构：
CREATE TABLE [dbo].[Customers](
[CustomerID] [nvarchar](36) NOT NULL,
[Fullname] [nvarchar](500) NOT NULL,
CONSTRAINT [PK_CRMCustomers] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CRMCustomers', @level2type=N'COLUMN',@level2name=N'CustomerID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'全称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CRMCustomers', @level2type=N'COLUMN',@level2name=N'Fullname'
GO
REATE TABLE [dbo].[Contacts](
[ContactID] [nvarchar](36) NOT NULL,
[CustomerID] [nvarchar](36) NOT NULL,
[Name] [nvarchar](500) NOT NULL,
[Telephone] [nvarchar](500) NULL,
CONSTRAINT [PK_CRMContacts] PRIMARY KEY CLUSTERED
(
[ContactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'联系人ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CRMContacts', @level2type=N'COLUMN',@level2name=N'ContactID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CRMContacts', @level2type=N'COLUMN',@level2name=N'CustomerID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CRMContacts', @level2type=N'COLUMN',@level2name=N'Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'移动电话' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CRMContacts', @level2type=N'COLUMN',@level2name=N'Telephone'
GO
如何取出全称重复或联系人手机号和别的客户的联系人手机号重复的客户？（注：考虑读取速度） SQL
[解决办法]
建议你给出测试数据 和想要的结果，
这样看真费劲
[解决办法]
select * from 客户表 where CustomerID in
(select CustomerID from 联系人表 where Telephone in (select Telephone from 联系人表
group by Telephone having count(Telephone)>1))
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 c.CustomerID,
c.Fullname
from 客户表 c
where exists
(select 1 from 客户表 d where d.CustomerID<>c.CustomerID and d.Fullname=c.Fullname)
or exists
(select 1 from 联系人表 e
where e.CustomerID<>c.CustomerID and e.Telephone in
(select Telephone from 联系人表 f where f.CustomerID=c.CustomerID))
/*
CustomerID Fullname
----------- --------------------
1 河北省分校
3 河北省分校
4 陕西省分校
5 江西省分校
(4 row(s) affected)
*/
