一个统计问题,谢谢了
表A
ID       NAME    
1           A          
2           B
3           C

表B
TABLE_ID                   DATA
    1                             XXXXX
    1                             XXXXX
    1                             XXXXX
    3                             YYYYY
    3                             YYYYY

希望返回
    ID         NAME       COUNTS
      1           A               3
      3           C               2
没有B,因为表B中没有B的数据

或者  
    ID         NAME       COUNTS
      1           A               3
      2           B               0
      3           C               2

2个的存储过程,谢谢了


------解决方案--------------------
select a.id,a.name,count(1) counts from a Inner Join b On a.id=b.id group by a.id,a.name
------解决方案--------------------
一、
select 表A.id as id,表A.name as name,count(*) as counts
from 表A,表B
where 表A.id=表B.TABLE_ID
group by 表A.id,表A.name
order by 表A.id

二、
select 表A.id as id,表A.name as name,isnull(表C.counts,0) as counts
from 表A left join (
select TABLE_ID,count(*) as counts from 表B group by TABLE_ID
) 表C on 表A.id=表C.TABLE_ID
------解决方案--------------------
select a.id,a.name,count(*) counts from a ,b where a.id=b.id group by a.id,a.name
------解决方案--------------------
if object_id( 'pubs..A ') is not null
drop table A
go

create table A(ID int,NAME varchar(10))
insert into A(ID,NAME) values(1, 'A ' )
insert into A(ID,NAME) values(2, 'B ')
insert into A(ID,NAME) values(3, 'C ')
go

if object_id( 'pubs..B ') is not null
drop table B
go

create table B(TABLE_ID int,DATA varchar(10))
insert into B(TABLE_ID,DATA) values(1, 'XXXXX ')
insert into B(TABLE_ID,DATA) values(1, 'XXXXX ')
insert into B(TABLE_ID,DATA) values(1, 'XXXXX ')
insert into B(TABLE_ID,DATA) values(3, 'YYYYY ')
insert into B(TABLE_ID,DATA) values(3, 'YYYYY ')
go

select A.id , A.name , t.counts from A,
(select table_id , count(*) counts from b group by table_id) t
where a.id = t.table_id

select A.id , A.name , isnull(t.counts,0) counts from A
left join
(select table_id , count(*) counts from b group by table_id) t