select
身份号2,
表1中出现次数=count(A.*),
表2中出现次数=count(B.*)
from
表1 A inner join 表2 B
on
A.身份号1=B.身份号2
group by
身份号2
身份号2,
表1中出现次数=count(A.*),
表2中出现次数=count(B.*)
from
表1 A inner join 表2 B
on
A.身份号1=B.身份号2
group by
身份号2
declare @t1 table(编号1 int identity(1,1),身份证1 varchar(10))
insert into @t1 select '3303'
union all select '3304'
union all select '3303'
union all select '3305'
union all select '3000'declare @t2 table(编号1 int identity(1,1),身份证2 varchar(10))
insert into @t2 select '3303'
union all select '3304'
union all select '3305'
union all select '3305'
union all select '3307'
union all select '3304'
--查询
select
身份号=A.身份证1,
表1中出现次数=(select count(*) from @t1 where 身份证1=A.身份证1),
表2中出现次数=(select count(*) from @t2 where 身份证2=A.身份证1)
from
@t1 A inner join @t2 B
on
A.身份证1=B.身份证2
group by
A.身份证1--结果
身份号 表1中出现次数 表2中出现次数
---------- ----------- -----------
3303 2 1
3304 1 2
3305 1 2(所影响的行数为 3 行)
select identity(int,1,1) as id,* into # from
(
select
身份号=A.身份证1,
表1中出现次数=(select count(*) from @t1 where 身份证1=A.身份证1),
表2中出现次数=(select count(*) from @t2 where 身份证2=A.身份证1)
from
@t1 A inner join @t2 B
on
A.身份证1=B.身份证2
group by
A.身份证1
) Tselect * from #id 身份号 表1中出现次数 表2中出现次数
----------- ---------- ----------- -----------
1 3303 2 1
2 3304 1 2
3 3305 1 2
select
身份号=T1.身份号1,
/*此处错误*/表1中出现次数=(select count(*) from T1 where 身份号1= T1.身份号1),
表2中出现次数=(select count(*) from T2 where 身份号2 = T1.身份号1)
from
T1,T2 /*需要改动处*/
where
身份号1 = 身份号1
group by
身份号1请问一下为什么使用了 T1 A, T2 B (这个应该是表的别名吧?) 后并将标明的错误处改成select count(*) from T1 where 身份号1= A.身份号1
就会成了正确的呢?能否解释一下.