sql练习(mysql版)

学生表student

老师表teacher

课程表course

分数表test

---

1.数学比语文成绩高的所有学生

select a.sid from
(select * from test where cid=1) a,
(select * from test where cid=2) b
where a.sid=b.sid
and a.score>b.score

 2.查询各科分数前三的学生

#第一种
select tmp.* from test as tmp
where 
(
select count(*) from test 
where cid = tmp.cid and score > tmp.score
)<3  
order by cid,score desc

#第二种
select tmp.* from test as tmp
where exists
(
select count(*) from test 
where cid = tmp.cid and score > tmp.score
having count(*)<3
)
order by cid,score desc

这个我是这么理解的，首先假设存在各科分数前三的表tmp，那么它的构成就是这样的

和原test表相比，有什么关联呢？

当满足tmp.cid=test.cid=1时，test.score中只有一个99满足>tmp中的其中一个数。

当tmp.cid=test.cid=2时，test.score中有2个数（98和91）满足>tmp中的其中一个数。

可以发现，test.score中，不能有三个数满足大于tmp中的数，不然的话，tmp将存在4个互不相同的数，那就不是前三了。

所以。。。当满足以下条件时，就能保证存在这么一个tmp表。

select count(*) from test 
where cid = tmp.cid and score > tmp.score
having count(*)<3

 3.查询每个学生数语英三门课的成绩，合并于一张表

SELECT tmp.sid,
(SELECT score FROM test WHERE tmp.sid=sid AND cid='1') AS 'math',
(SELECT score FROM test WHERE tmp.sid=sid AND cid='2') AS 'chinese',
(SELECT score FROM test WHERE tmp.sid=sid AND cid='3') AS 'english'
FROM test AS tmp
GROUP BY tmp.sid
ORDER BY tmp.sid

 4.查询每个学生的数学成绩，并在成绩后面注明是否及格

select sid,score,if(score<60,'不及格','及格') as '是否及格' 
from test where cid=1