简单的count

[sunhood]

有3个表都有编号列，统计每个表中同一编号的记录数。
大致如下：

  表1                   表2                  表3
 code                  code                code
-------             -----------          -----------
ABCD			ABCD		   
ABCD			ABCD		   
ABCD					   
ABCD					   		

希望得到如下结果

 code                  表1(此处用a1，a2标识也可)       表2                   表3
-------             -----------                 -----------          -------
ABCD			4		            2                      0

[wzjpsq]

left join

[wzy_love_sly]

select code='abcd',
a1=(select count(1) from 表1 where code='abcd'),
a2=(select count(1) from 表2 where code='abcd'),
a3=(select count(1) from 表3 where code='abcd')

[liangCK]

select code
      ,表1=(select count(code) from 表1 where code=a.code)
      ,表2=(select count(code) from 表2 where code=a.code)
      ,表3=(select count(code) from 表3 where code=a.code)
from
(
  select code from 表1
  union
  select code from 表2
  union
  select code from 表3
) a

[sunhood]

code列中会有很多编码比如：ABCD, AAAA,BBBB.......该怎么count?

[sunhood]

用left outer join或union 我可以写，但是尽量不想用left outer 或者union，该怎么写？

[liangCK]

就3楼的去写吧.

[sunhood]

有没有更简便的写法？？

[ruihuahan]

select ....
from (select code, count(*) from table1) t1
inner join (select code, count(*) from table2) t2
on t1.code=t2.code
inner join (select code, count(*) from table3) t3
on t2.code=t3.code

[中国风]

select 
	code,
	[表1]=sum(case when TName='表1' then 1 else 0 end),
	[表2]=sum(case when TName='表2' then 1 else 0 end),
	[表3]=sum(case when TName='表3' then 1 else 0 end)

from 
	( select code ,'表1' as TName from 表1 
	  union
	  select code ,'表2'from 表2 
	  union
	  select code ,'表3'from 表3 )T
where 
	code='abcd'

[cxmcxm]

select code=case when a.code is not null then a.code else 
				case when b.code is not null then b.code else c.code end end,
	   a1=isnull(a.a1,0),a2=isnull(b.a2,0),a3=isnull(c.a3,0)
						
from 
(select code,count(*) a1 from 表1 group by code) a full join 
(select code,count(*) a2 from 表2 group by code) b on a.code=b.code full join 
(select code,count(*) a3 from 表3 group by code) c on a.code=c.code and b.code=c.code

[-狙击手-]

elect 
    code,
    [表1]=sum(case when TName='表1' then 1 else 0 end),
    [表2]=sum(case when TName='表2' then 1 else 0 end),
    [表3]=sum(case when TName='表3' then 1 else 0 end)

from 
    ( select code ,'表1' as TName from 表1 
      union
      select code ,'表2'from 表2 
      union
      select code ,'表3'from 表3 )T
group by code

[cxmcxm]

改改9楼的

select 
    code,
    [表1]=sum(case when TName='表1' then 1 else 0 end),
    [表2]=sum(case when TName='表2' then 1 else 0 end),
    [表3]=sum(case when TName='表3' then 1 else 0 end)

from 
    ( select code ,'表1' as TName from 表1 
      union
      select code ,'表2'from 表2 
      union
      select code ,'表3'from 表3 )T
group by code