表結(jié)構(gòu)如下：
- 學(xué)生資料表
Paste_Image.png
- 學(xué)生的成績表，sid就是學(xué)生資料表的外鍵
Paste_Image.png
分數(shù)最高的學(xué)生信息澜建，科目
方法一：左連接
select s.course as 科目,max(s.score1) as 最高分,c.name as 姓名 from score s left join student c on c.id = s.sid group by s.course;
方法二：子查詢
select course as 科目,max(score1) as 最高分,(select name from student c where c.id=s.sid) as 姓名 from score s group by course;
Paste_Image.png
科目評價為良好,性別為女的學(xué)生信息向挖，科目
select s.score1,s.course,c.name,c.sex from score s join student c on s.sid=c.id where s.comment='良好' and c.sex='女';
Paste_Image.png
最低分數(shù)性別為男和女的信息
select course,min(score1) as 最低分,c.sex,c.name from score s join student c on s.id=c.id and c.sex='男' or sex='女' group by course;
Paste_Image.png
每科的前兩名學(xué)生信息
select course,score1, c.name from score s1 left join student c on c.id=s1.sid where 2>(select count(*) from score s where s.course = s1.course and s.score1>s1.score1) order by s1.course,s1.score1
Paste_Image.png
還有其他的兩種方法可以取前兩名學(xué)生數(shù)據(jù)：
方法二：select a.id,a.SName,a.ClsNo,a.Scorefrom Table1 a left join Table1 b on a.ClsNo=b.ClsNo and a.Score<b.Scoregroup by a.id,a.SName,a.ClsNo,a.Scorehaving count(b.id)<2order by a.ClsNo,a.Score desc
方法三：select *from Table1 awhere id in (select id from Table1 where ClsNo=a.ClsNo order by Score desc limit 2)order by a.ClsNo,a.Score desc
