sqlserver多表联合查询_sqlserver多表联查时count怎么用-优快云博客

本文介绍如何在SQLServer中进行多表联合查询，并通过具体示例演示如何获取教师基本信息及其它相关信息的数量。示例中使用了UNION ALL来组合多个查询结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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]