select A.AID,A.AName,case isnull(max(B.AID),0) when 0 then 0 else COUNT(*) end as count  from A left join B on A.AID=B.AID group by A.AID,A.AName

------解决方案--------------------

SQL code

select a.aid,a.aname,count(b.aid) c from #tempA a left join #tempB b on a.aid=b.aid group by a.aid,a.aname
/*
aid aname c
1    a      4
2    b      2
3    c      2
4    d      1
5    e      0
*/

------解决方案--------------------

第一种情况:不显示A表中无关记录
SQL code

use DBTest
go
if  OBJECT_ID('A') is not null drop table A
go
if OBJECT_ID('B') is not null drop table B
go
create table A
(
AID int,
AName nvarchar(20)
)
create table B
(
BID int,
AID int,
step int
)
insert into A
select 1,'A' union all
select 2,'B'
insert into B
select 1,1,1 union all
select 2,1,2

--第一个
select A.AID,A.AName,COUNT(*) as count from A inner join B
on A.AID=B.AID group by A.AID,A.AName

--第二个
select A.AID,A.AName,sum(case B.step when 1 then 1 else 0 end ) as count1,
sum(case B.step when 2 then 1 else 0 end ) as count2 from A inner join B
on A.AID=B.AID group by A.AID,A.AName