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 使用注意：查询出的列数必须相同