公司的SQL语句问题，高手自己看着玩
设学校开设的每门课程都指定了任课教师，每门课程只能有一个任课教师，
而每名教师可以教授多门课程；每名学生可以选多门课程，每门课程可以被
多名学生选。以下为数据库中的关于成绩信息的表，带“*”为主键，请阅读
后写出相应的SQL语句。
学生表（*学号，姓名）
课程表（*课程号，课程名，授课教师）
选课表（*学号，*课程号，成绩）
(1) 列出所有学了张三学过的所有课程的学生。即假定张三学了A、B、C三
门课，则列出所有学了A、B、C三门课的学生（超过三门课也符合），只
学了其中一两门的则不符合。
(2) 列出两门课以上（含）不及格或根本没有学任何课程的学生，不及格即
成绩６０分以下（不含）。
(3) 列出所有教师及其所教课程中学生最多的课程名，学生数。即假定其中某
教师W教了A、B、C三门课，经过统计后A、B、C三门课各有32、46、38
名学生学过，则显示：
教师 课程名 学生
W B 46
... ... ...
问题点数：0、回复次数：23Top
1 楼LDH202（玲海）回复于 2004-08-03 13:55:44 得分 0
upTop
2 楼LDH202（玲海）回复于 2004-08-03 14:44:08 得分 0
upTop
3 楼sankis（）回复于 2004-08-03 14:47:46 得分 0
upTop
4 楼txliceuhe（决不冒牌马可）回复于 2004-08-03 14:49:12 得分 0
1.select s.学号，s.姓名
from 学生表 s
where
not exist (select 1
from 选课表 k2,学生表 s2
where k2.学号=s2.学号 and s2.姓名='张三'
and not exists (select 1 from 选课表 k where k.学号=s.学号 and k.课程=k2.课程)
)
2、select s.学号，s.姓名
from 学生表 s ，选课表 k
where k.学号 *= s.学号
group by s.学号，s.姓名
having sum(case when 成绩 <=60 then 1 else 0) >2 or sum(成绩) is null
3、
select 授课教师,课程名，count(*) 学生
from 课程表 k,选课表 s
where k.课程号=s.课程号
group by 授课教师,课程名
having count(*) =
(select top 1 count(*) from 课程表 k2,选课表 s2
where k2.课程号=s2.课程号
and k2.授课教师=k.授课教师
group by 授课教师,课程名
order by count(*) desc
)
Top
5 楼txliceuhe（决不冒牌马可）回复于 2004-08-03 14:49:52 得分 0
未测试居然花了12分钟，老了。Top
6 楼txliceuhe（决不冒牌马可）回复于 2004-08-03 14:51:22 得分 0
建议：不要用中文字段名，不然写语句很慢！Top
7 楼LDH202（玲海）回复于 2004-08-03 14:55:33 得分 0
Mr. txliceuhe(决不冒牌马可)
第三题，当有两门课程学生数一样且都为最多时，会有问题吧。。。
哦，题目里忘了强调列表时每个教师最多一行Top
8 楼LDH202（玲海）回复于 2004-08-03 14:57:20 得分 0
to Mr. txliceuhe(决不冒牌马可)
还是很强的，这是公司的面试题目，So many people花了半个小时也只搞定前两道Top
9 楼txliceuhe（决不冒牌马可）回复于 2004-08-03 15:05:44 得分 0
报告楼上：可以，不信你试试。
最多一行啊，Too Simple 哈哈
select 授课教师,课程名，count(*) 学生
from 课程表 k,选课表 s
where k.课程号=s.课程号
group by 授课教师,s.课程号,课程名
having s.课程号=
(select top 1 s.课程号 from 课程表 k2,选课表 s2
where k2.课程号=s2.课程号
and k2.授课教师=k.授课教师
group by 授课教师,课程名
order by count(*) desc
)Top
10 楼CSDMN（冒牌经理 V0.4）回复于 2004-08-03 15:05:44 得分 0
第三题，建议楼主测试后再评判
Top
11 楼txliceuhe（决不冒牌马可）回复于 2004-08-03 15:07:12 得分 0
楼上：
确实可能出现多行，但是我认为既然都是最多就要一起显示，所以没用第二种方法Top
12 楼CSDMN（冒牌经理 V0.4）回复于 2004-08-03 15:07:36 得分 0
不过建议txliceuhe(决不冒牌马可) 改成：
select 授课教师,课程名，count(*) 学生
from 课程表 k,选课表 s
where k.课程号=s.课程号
group by 授课教师,s.课程号,课程名
having s.课程号=
(select top 1 s.课程号 from 课程表 k2,选课表 s2
where k2.课程号=s2.课程号
and k2.授课教师=k.授课教师
group by 课程名
order by count(*) desc
)
好理解
Top
13 楼txliceuhe（决不冒牌马可）回复于 2004-08-03 15:08:33 得分 0
select 授课教师,课程名，count(*) 学生
from 课程表 k,选课表 s
where k.课程号=s.课程号
group by 授课教师,s.课程号,课程名
having s.课程号=
(select top 1 s.课程号 from 课程表 k2,选课表 s2
where k2.课程号=s2.课程号
and k2.授课教师=k.授课教师
group by 授课教师, s.课程号
order by count(*) desc
)
我就说中文字段名不好！
Top
14 楼victorycyz（--）回复于 2004-08-03 15:14:21 得分 0
是啊，如果把表名、字段名都改为a、b、c之类的，用时可缩短一半以上。
Top
15 楼txliceuhe（决不冒牌马可）回复于 2004-08-03 15:26:29 得分 0
无聊我也想1题，耗点时间的：
计算各老师各门课程的效果：
效果＝成绩/热门程度
效果这样算，假设总共有10个课程，那么热门成绩用 选修 总课程数/人数排名 判断，也就是人数最多的 是 10/1=10 ,第二的是10/2=5
成绩 用 本课程的 总课程数/平均分数排名 计算，平均分数排名是本课程平均分在所有课程平均分里的排名Top
16 楼txliceuhe（决不冒牌马可）回复于 2004-08-03 15:27:38 得分 0
不考能不能写出来，考谁写得最短，性能最好。Top
17 楼dhl2001（暗夜行路）回复于 2004-08-03 15:27:58 得分 0
是啊，如果把表名、字段名都改为a、b、c之类的，用时可缩短一半以上。
没有道理的胡说Top
18 楼LDH202（玲海）回复于 2004-08-03 15:30:01 得分 0
to Mr. txliceuhe(决不冒牌马可)
第二种方法就没有问题了，Well done
可惜公司的Money很少，不然就挖你 ^-^Top
19 楼rouqu（石林#黄果树）回复于 2004-08-03 15:46:11 得分 0
没事写着玩倒是有点意思Top
20 楼rea1gz（冒牌realgz V0.4）回复于 2004-08-03 15:49:26 得分 0
21 楼rea1gz（冒牌realgz V0.4）回复于 2004-08-03 15:50:16 得分 0
没分有什么好玩的
不如去看看：
http://community.csdn.net/Expert/topic/3238/3238322.xml?temp=.9951898
Top
22 楼icehouse（冰室之UP无罪）回复于 2004-08-03 16:16:51 得分 0
不错Top
23 楼rouqu（石林#黄果树）回复于 2004-08-03 17:19:37 得分 0
晕 楼主咋不给分啊Top
