sqlserver多表联合查询

sqlserver多表联合查询

新手第一篇文章，不足之处多多指教

例如：表A里面存储教师基本信息；表B存储教师获奖情况；表C存储教师出书情况；表D存储教师学术论文情况
现在需要查询出教师基本信息以及另外三个表中关于该教师其他情况的记录条数。
表A
ID WorkId Name BirthDay
表B
ID WorkId Reward
表C
ID WorkId Book
表D
ID WorkId Paper

select *, 0 AS NUM  from A WHERE A.WorkID='1234'
union all 
select A.*, COUNT(B.ID) AS NUM FROM A,B WHERE A.WorkId='1234'  AND A.WorkId=B.WorkId GROUP BY A.[ID] ,A.[WorkId],A.[Name],A.[Birthday]
union all 
select A.*, COUNT(C.ID) AS NUM FROM A,C WHERE A.WorkId='1234'  AND A.WorkId=C.WorkId GROUP BY A.[ID] ,A.[WorkId],A.[Name],A.[Birthday]
union all 
select A.*, COUNT(D.ID) AS NUM FROM A,D WHERE A.WorkId='1234'  AND A.WorkId=D.WorkId GROUP BY A.[ID] ,A.[WorkId],A.[Name],A.[Birthday]

GROUP BY 后面必须列出表A里面所有列名
UNION ALL 使用注意：查询出的列数必须相同