declare @tabA table(TID int,TName nvarchar(10))
insert into @tabA
select 37,N'美丽天涯海' union all
select 38,N'美丽天涯海' union all
select 39,N'美丽天涯海角'
declare @tabB table(TDID int,TID int,Status int)
insert into @tabB
select 9,38,2 union all
select 11,38,2 union all
select 14,39,1 union all
select 15,39,2 union all
select 16,39,1 union all
select 17,39,2

select a.TID,a.TName,sum(case when b.Status=2 then 1 else 0 end) Status为2的个数,sum(case when b.Status=1 then 1 else 0 end) Status为1的个数   from @tabA a,@tabB b
where a.TID=b.TID  
group by a.TID,a.TName

/*
TID         TName      Status为2的个数 Status为1的个数
----------- ---------- ----------- -----------
38          美丽天涯海      2           0
39          美丽天涯海角     2           2
*/