关于多表统计数量的SQL语句,该怎么解决

关于多表统计数量的SQL语句
A表：
AID AName
1 a
2 b
3 c
4 d
5 E
B表
BID AID
1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 3
9 3
10 4

想通过SQL语句查询出如下表样式的结果：
AID AName count
1 a 4
2 b 3
3 c 2
4 d 1
5 e 0

count是统计B表中有AID的数量的，这样的SQL语句怎么写？！求指导！！！！！！！！！！！！！！！！！！！

------解决方案--------------------
select A.AID,A.AName,count(*) as count from A inner join B on A.AID=B.AID group by A.AID,A.AName
------解决方案--------------------
内连接与外连接的问题啊
------解决方案--------------------

SQL code

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

关于多表统计数量的SQL语句,该怎么解决

相关推荐