有表A(SchoolID,SchoolName)和B(DeptID,ReferSchool),
B中的多条记录对应A中的一条记录,ReferSchool为外键,指向A的SchoolID字段。现在想查询A中的SchoolID分别为s1、s2、s3的三所学校分别有几个部门(即A中三条记录分别对应B中的几条记录),用一条SQL语句写出,应该怎样写呢?例如
表A数据:
SchoolID | SchoolName
----------------------
s1 | 一中
s2 | 二中
s3 | 三中
s4 | 四中
s5 | 五中表B数据:
DeptID | ReferSchool
---------------------
d11 | s1
d12 | s1
d13 | s1
d21 | s2
d22 | s2
d31 | s3
d32 | s3
d33 | s3
d34 | s3
d35 | s3
... | ...需要得出预期结果:
SchoolID | HasDept
------------------
s1 | 3
s2 | 2
s3 | 5请各位高手拔刀相助哦!先谢谢啦!:-)
B中的多条记录对应A中的一条记录,ReferSchool为外键,指向A的SchoolID字段。现在想查询A中的SchoolID分别为s1、s2、s3的三所学校分别有几个部门(即A中三条记录分别对应B中的几条记录),用一条SQL语句写出,应该怎样写呢?例如
表A数据:
SchoolID | SchoolName
----------------------
s1 | 一中
s2 | 二中
s3 | 三中
s4 | 四中
s5 | 五中表B数据:
DeptID | ReferSchool
---------------------
d11 | s1
d12 | s1
d13 | s1
d21 | s2
d22 | s2
d31 | s3
d32 | s3
d33 | s3
d34 | s3
d35 | s3
... | ...需要得出预期结果:
SchoolID | HasDept
------------------
s1 | 3
s2 | 2
s3 | 5请各位高手拔刀相助哦!先谢谢啦!:-)
select ReferSchool SchoolID,Count(DeptID) HasDept from 表B group by ReferSchool
FROM A,B
WHERE A.SCHOOLID=B.SCHOOLID AND A.SCHOOLID IN ('s1','s2','s3')
GROUP BY A.SCHOOLNAME,A.SCHOOLID ORDER BY A.SCHOOLID
from 表A a,表B b
where a.SchoolID=b.ReferSchool(+)
and a.SchoolID in ('s1','s2','s3')
group by a.SchoolID
FROM A,B
WHERE A.SchoolID=B.ReferSchool
GROUP BY A.SchoolID
SELECT A.SCHOOLNAME,A.SCHOOLID,COUNT(*) HASDEPT
FROM A,B
WHERE A.SCHOOLID=B.SCHOOLID AND A.SCHOOLID IN ('s1','s2','s3')
GROUP BY A.SCHOOLNAME,A.SCHOOLID ORDER BY A.SCHOOLID