--建立测试表 tb1,tb2（#表示临时表）
create table #tb1(id varchar(12),name varchar(20))
create table #tb2(acct varchar(12),name varchar(20))
insert into #tb1
select 'A123456789','Chris' union all
select 'A123456789','Chris' union all
select 'A123456789','Chris' union all
select 'A123456789','Chris' union all
select 'A100000002','Chris' union all
select 'A100000001','Mary'
insert into #tb2
select 'A123456789','March' union all
select 'A123456789','Chris' union all
select 'A100000003','Chris' union all
select 'A100000001','Anmi'
--你的要的答案不明确，不知道你要统计那个表的记录数，有下面3种情况
--统计1、 显示“表1在表2存在”的 ID，笔数
select a.id,count(*) as cnt from #tb1 a where a.id in(select distinct acct from #tb2 ) group by a.id
--统计2、 显示”表2在表1存在“的 ACCT,笔数
select b.acct ,count(*) as cnt from #tb2 b where b.acct in(select distinct id from #tb1 ) group by b.acct
--统计3、 显示“2个表都存在”的，记录总次
select c.id,count(*)总次 from(
select a.id,a.name from #tb1 a where a.id in(select distinct acct from #tb2)
union all
select b.acct,b.name from #tb2 b where b.acct in(select distinct id from #tb1)
)c
group by c.id
--删除测试表
drop table #tb1
drop table #tb2
[此贴子已经被作者于2016-10-17 15:03编辑过]
