当前位置: 代码迷 >> Sql Server >> MSSQL多表查询(一个基表,两个附表求和)有关问题
  详细解决方案

MSSQL多表查询(一个基表,两个附表求和)有关问题

热度:95   发布时间:2016-04-27 18:40:26.0
MSSQL多表查询(一个基表,两个附表求和)问题
三个表:
1.学校表(school):id,schoolname,createdate
2.学生表(student):id,username,schoolid,createdate
3.评论表(comment):id,comment,schoolid,createdate

现在想用一条SQL语句获得如下数据(不用存储过程):
select school.id, school.schoolname, count(student.id) as countstudent, count(comment.id) as countcomment from ... where (or ...on) school.id=student.schoolid and school.id=comment.schoolid

请高手赐教,不胜感谢!

------解决方案--------------------
SQL code
select id, schoolname,     ( select count(*) from student where schoolid = a.id ) as countstudent,    ( select count(*) from comment where schoolid = a.id ) as countcommentfrom school a
  相关解决方案