面试的题没有写出来，请高手帮看看,该怎么处理

面试的题没有写出来，请高手帮看看
有3张表
S(s#,sn,sd,sa)表示，学生的学号，姓名，所属单位，年龄
C（c#,cn）分别代表课程编号，课程名称
SC（s#,c#,G）分别代表学号，选修的课程编号，学习的成绩
问：
运用嵌套查询，查询选修全部课程的学员姓名和所属单位。
我用的Mysql


[解决办法]
select s# from 
 (
select count(s#) as column1,s# from 
( select * from sc group by s#,c#) as a
group by a.s# 
 ) as b 
where b.column1 = (select count(*) from c);
[解决办法]
select sn,sd from S where s# in (
(select b.s# from
(select count(c#) no1 from C ) a , 
(select s#,count(c#) no2 from SC group by s# ) b where a.no1=b.no2))
[解决办法]

探讨

select sn,sd from S where s# in (
(select b.s# from
(select count(c#) no1 from C ) a ,
(select s#,count(c#) no2 from SC group by s# ) b where a.no1=b.no2))

[解决办法]
select s.s#,s.sn,s.sd from studyInfoTable s 
inner join
(select sc.s# 
 from (select s#,count(c#)as sun1 from scInfoTable group by s#)sc ,
(select count(c#)as sun2 from classInfoTable) c 
 where sc.sun1=c.sun2)ss 
 on s.s#=ss.s#

查看更多 下一篇