sql语句的考察

例如sql表是这样的：

image.png

写了几个我当时没写出来的查询，记录一下。

-- 插叙不及格数大于等于2门的学生的平均成绩
 select name,sum(chengji<60) as jimen, avg(chengji) as pingjun from student GROUP BY name having jimen >=2;

-- 查询每个学生的最好成绩的科目
select t1.name,t1.chengji,t1.kemu from student t1,
 (select name,max(chengji) as maxchengji,kemu from student GROUP BY name) t2
 where t1.name = t2.name and t1.chengji = t2.maxchengji;
 
-- 查询每个科目最好成绩的同学
select t1.name,t1.kemu,t1.chengji from student t1,
 (select max(chengji) as maxchengji,kemu from student group by kemu) t2
 where t1.kemu = t2.kemu and t1.chengji = t2.maxchengji;
 
 
 -- 数学成绩排名
select name,chengji,
(select count(*) from student t1 where kemu ='数学' and t1.chengji > t2.chengji)+1 as 名次 from student t2  
where kemu='数学' order by chengji desc;