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',2create 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',1select 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.sidfid 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', 2declare @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', 1select 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(4 row(s) affected)Warning: Null value is eliminated by an aggregate or other SET operation.
insert S select 1,'s1',1
union all select 2,'s2',1
union all select 3,'s3',2
union all select 4,'s4',2create 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',1select 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.sidfid sid howmany_a_in_s
----------- ----------- --------------
1 1 3
1 2 0
2 3 1
2 4 0(所影响的行数为 4 行)
insert @s select 1,' s1', 1
union all select 2, 's2', 1
union all select 3, 's3', 2
union all select 4,' s4', 2declare @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', 1select
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(4 row(s) affected)Warning: Null value is eliminated by an aggregate or other SET operation.