这样的SQL了怎么写
表F
fid     fname
1           f1
2           f2
表S
sid     sname     fid
1           s1           1
2           s2           1
3           s3           2
4           s4           2
表A
aid     aname     sid
1           a1           1
2           a2           1
3           a3           3
4           a4           1

需要得到如下查询结果
显示表S的全部内容，统计每个s下面有多少个a

fid       sid         howmany_a_in_s
1             1                     3
1             2                     0
2             3                     1
2             4                     0

------解决方案--------------------
select fid,
sid,
howmany_a_in_s=(select count(*) from 表A where sid=表S.sid)
from 表S
------解决方案--------------------
相关子查询~
同楼上
------解决方案--------------------
create table S(sid int,sname varchar(10),fid int)
insert S select 1, 's1 ',1
union all select 2, 's2 ',1
union all select 3, 's3 ',2
union all select 4, 's4 ',2

create table A(aid int,aname varchar(10),sid int)
insert A select 1, 'a1 ',1
union all select 2, 'a2 ',1
union all select 3, 'a3 ',3
union all select 4, 'a4 ',1

select fid,S.sid,howmany_a_in_s=count(A.sid) from S full outer join A on S.sid=A.sid
group by fid,S.sid

fid sid howmany_a_in_s
----------- ----------- --------------
1 1 3
1 2 0
2 3 1
2 4 0

（所影响的行数为 4 行）

------解决方案--------------------
declare @S table(sid int, sname nvarchar(5),fid int)
insert @s select 1, ' s1 ', 1
union all select 2, 's2 ', 1
union all select 3, 's3 ', 2
union all select 4, ' s4 ', 2

declare @A table (aid int, aname nvarchar(5), sid int)
insert @a select 1, ' a1 ', 1
union all select 2, ' a2 ', 1
union all select 3, ' a3 ', 3
union all select 4, 'a4 ', 1

select
s. fid,
s.sid,
[howmany_a_in_s]=count(a.sid)
from @s s left join @a a
on s.sid=a.sid
group by
s. fid,
s.sid

(4 row(s) affected)


(4 row(s) affected)

fid sid howmany_a_in_s
----------- ----------- --------------
1 1 3
1 2 0
2 3 1
2 4 0