sql查询复习笔记-小技巧，大智慧（续一）

3.回答有关“至少”的问题
前言：“至多”的对立面是“至少”。通常，采用“至多”问题中表述的技巧变体，就可以解决“至少”问题。当解决“至少”问题时，把他们换成“没有更少的”说法会更好理解。
问题（6）：找到至少选择两门课程的学生。

-- 通过聚集函数count
select sd.Tid,sd.Name,sd.Age from Student sd,StudentTakeCourses stc
where sd.Tid=stc.Sid
group by sd.Tid,sd.Name,sd.Age
having count(*)>=2
--使用自连接查询
select * from Student where Tid  in
(select stca.sid from StudentTakeCourses stca,StudentTakeCourses stcb
where stca.sid=stcb.sid and stca.cid>stcb.cid
 )

问题（7）：找到同时选取sqlserver和mysql课程的学生。

-- 通过聚集函数count
select sd.Tid,sd.Name,sd.Age from Student sd,StudentTakeCourses stc
where sd.Tid=stc.Sid
and stc.cid in (select tid from Course where name='Sql Server' or name='My Sql')
group by sd.Tid,sd.Name,sd.Age
having count(stc.cid) >=2

-- 通过聚集函数min和max
select sd.Tid,sd.Name,sd.Age from Student sd,StudentTakeCourses stc
where sd.Tid=stc.Sid
and stc.cid in (select tid from Course where name='Sql Server' or name='My Sql')
group by sd.Tid,sd.Name,sd.Age
having min(stc.cid) <>max(stc.cid)

--使用自连接查询
select sd.* from Student sd,StudentTakeCourses stca,StudentTakeCourses stcb
where sd.Tid=stca.sid and stca.sid=stcb.sid
and stca.cid =(select tid from Course where name='Sql Server')
and stcb.cid=(select tid from Course where  name='My Sql')

问题（8）：找到至少比两个学生大的学生。

-- 通过聚集函数count
select sd.* from Student sd
where 2<=(select count(*) from Student sd1 where sd1.age<sd.age)


--使用自连接查询
select distinct sd.* from Student sd,Student sd1,Student sd2
where sd.age>sd1.age and sd1.age>sd2.age

4.回答有关“准确”的问题
问题（9）：找到只教一门课程的教授。

-- 通过聚集函数count
select pf.* from Professor pf,ProfessorTeachCourses ptc
where pf.Tid=ptc.pid 
group by pf.Tid,pf.Name,pf.Dept,pf.Salary,pf.Age
having count(*)=1

--使用自连接查询
select pf.* from Professor pf,ProfessorTeachCourses ptc
where pf.Tid=ptc.pid 
and pf.Tid not in
( select ptc1.Pid from ProfessorTeachCourses ptc1,ProfessorTeachCourses ptc2
where ptc1.Pid=ptc2.Pid and ptc1.Cid >ptc2.Cid
)

问题（10）：找到只选择sqlserver和mysql的学生（只选取了这两门课程，没有选择其他课程）

--使用自连接查询
select sd.* from Student sd,StudentTakeCourses stc1,StudentTakeCourses stc2
where sd.Tid=stc1.Sid and stc1.Sid =stc2.Sid 
and stc1.Cid=(select Tid from Course where Name='Sql Server')
and stc2.Cid=(select Tid from Course where Name='My Sql')
--排除选课超过三门的学生
and sd.Tid not in(
select sd1.Tid from Student sd1,StudentTakeCourses stca,StudentTakeCourses stcb,StudentTakeCourses stcd
where sd1.Tid=stca.Sid
and sd1.Tid=stcb.Sid
and stca.Cid>stcb.Cid
and stcb.Cid>stcd.Cid
)

问题（11）：找到只比两个学生大的学生（也就是找到第三年轻的学生）

-- 通过聚集函数count
select sd.* from Student sd
where 2=(select count(*) from Student sd1
where sd1.Age<sd.Age
)

--使用自连接查询
select sd1.* from Student sd1,Student sd2,Student sd3
where sd1.age>sd2.age and sd2.age>sd3.age
and sd1.Tid not in
--排除比三个学生以上大的学生
(select sd4.Tid from Student sd4,Student sd5,Student sd6,Student sd7
where sd4.age>sd5.age and sd5.age>sd6.age and sd6.age>sd7.age
)

5：回答有关“一些”或“所有”的问题
问题（12）：找到选取所有课程的学生。

select sd.Tid,sd.Name,sd.Age from Student sd,StudentTakeCourses stc
where sd.Tid=stc.Sid
group by sd.Tid,sd.Name,sd.Age 
having count(stc.Cid)=(select count(*) from course) --根据课程数排除课程没有都选的学生

问题（13）：找到比其他所有学生都大的学生。（找到年龄最大的学生）

--使用max聚集函数
select * from Student where Age=(select max(Age) from Student)

还有一种比较常见的方式：

--常见方式
select * from Student where Age>=all (select Age from Student)

最后通过自连接也可以查询到结果，不过没有上面两种看起来直接了当：

--自连接方式
select * from Student 
where Age not in 
(select sd1.Age from Student sd1,student sd2
where sd1.Age<sd2.Age
)

<完>