自己写了第一个 但感觉肯定不用这么麻烦
select tname from teacher
where tid in
( select tid from (select tid,count(tid) [count] from course group by tid) b where
b.[count] in (select max(a.[count]) from
(select tid,count(tid) [count] from course group by tid) a) )
select tname from teacher
where tid in
( select tid from (select tid,count(tid) [count] from course group by tid) b where
b.[count] in (select max(a.[count]) from
(select tid,count(tid) [count] from course group by tid) a) )
select *
from teacher
where tid in(
select tid from course group by tid
having count(*)>all(
select count(*) from course group by tid))
select *
from course join teacher
on course.tid=teacher.tid
where cid in(
select cid from sc group by cid
having count(*)<all(
select count(*) from sc group by cid))
select cname, tname
from course join teacher
on course.tid=teacher.tid
where cid in(
select cid from sc group by cid
having count(*)<all(
select count(*) from sc group by cid))--问题3
select cid, 不及格=count(*)
from sc
where grade<60
group by cid
from teacher
where tid in(
select tid from course group by tid
having count(*)>=all(
select count(*) from course group by tid))
你这个 应该是>=
drop table student
if exists (select name from sysobjects where name = 'sc')
drop table sc
if exists (select name from sysobjects where name = 'course')
drop table course
if exists (select name from sysobjects where name = 'teacher')
drop table teachercreate table student(sid int ,sname varchar(10))
insert into student
select 1,'学生1' union
select 2,'学生2' union
select 3,'学生3' union
select 4,'学生4' union
select 5,'学生5'
create table sc (sid int,cid int,grade int)
insert into sc
select 1,1,90 union
select 1,2,90 union
select 1,3,90 union
select 1,4,90 union
select 2,1,80 union
select 2,4,80 union
select 3,3,70 union
select 4,4,80
create table course(cid int ,cname varchar(10),tid int)
insert into course
select 1,'课程1',1 union
select 2,'课程2',2 union
select 3,'课程3',3 union
select 4,'课程4',1
create table teacher(tid int,tname varchar(10),deptname varchar(10))
insert into teacher
select 1,'老师1','计算机' union
select 2,'老师2','工程' union
select 3,'老师3','计算机'
from course join teacher
on course.tid=teacher.tid
group by deptname
having count(*)<all(
select count(*)
from course join teacher
on course.tid=teacher.tid
group by deptname)
这么快就写出来了这么多
什么时候我写SQL 有这种速度 而且正确率高 就好了
学习 :)
select *
from teacher
where tid in(
select tid from course group by tid
having count(*)>=all(
select count(*) from course group by tid))
--问题2:查询选课人数最少的课程的课程名和教师的姓名
select cname, tname
from course join teacher
on course.tid=teacher.tid
where cid in(
select cid from sc group by cid
having count(*)<=all(
select count(*) from sc group by cid))--问题3:统计每门课不及格的学生人数
select cid, 不及格=count(*)
from sc
where grade<60
group by cid--问题4:查找教授开课最少的系
select distinct deptname
from course join teacher
on course.tid=teacher.tid
group by deptname
having count(*)<=all(
select count(*)
from course join teacher
on course.tid=teacher.tid
group by deptname)
你的having 都没有写 =
测试 一个都查不出来:)
别笑我了.
刚才的代码都没几个是对了,细节都有待测试才行.不了意思.