有表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(1)cnt from b group by ReferSchool--如果楼主要学校名的话,可以
select SchoolID,SchoolName,cnt[hasdept] from a,
(select ReferSchool,count(1)cnt from b group by ReferSchool)c
where a.schoolid=c.ReferSchool
hasdept = count(1)
from Tableb
group by referschool
from A
inner join (select ReferSchool, count(*) as Cnt from B group by ReferSchool) as t
on A.SchoolID = t.ReferSchool
where a.schoolid= b.ReferSchool
group by a.ReferSchool,a.schoolname
select a.SchoolID , isnull(t.HasDept,0) HasDept from A
left join
(select ReferSchool , count(*) HasDept from b group by ReferSchool) t
on A.SchoolID = t.ReferSchool
insert into A values('s1', '一中')
insert into A values('s2', '二中')
insert into A values('s3', '三中')
insert into A values('s4', '四中')
insert into A values('s5', '五中')
create table B(DeptID varchar(10) , ReferSchool varchar(10))
insert into B values('d11', 's1')
insert into B values('d12', 's1')
insert into B values('d13', 's1')
insert into B values('d21', 's2')
insert into B values('d22', 's2')
insert into B values('d31', 's3')
insert into B values('d32', 's3')
insert into B values('d33', 's3')
insert into B values('d34', 's3')
insert into B values('d35', 's3')
goselect a.SchoolID,count(*) HasDept from a,b where a.SchoolID = b.ReferSchool group by a.SchoolID
/*
SchoolID HasDept
---------- -----------
s1 3
s2 2
s3 5(所影响的行数为 3 行)
*/--不过最好用左连接
select a.SchoolID , isnull(t.HasDept,0) HasDept from A
left join
(select ReferSchool , count(*) HasDept from b group by ReferSchool) t
on A.SchoolID = t.ReferSchool
/*
SchoolID HasDept
---------- -----------
s1 3
s2 2
s3 5
s4 0
s5 0
(所影响的行数为 5 行)
*/drop table A,B