图解面试题：经典50题

/*【知识点】分组查询分析思路select 查询结果 [总成绩：sum(成绩), 学号]from 从哪张表中查找数据 [成绩表score]where 查询条件 [没有]group by 分组 [学生的总成绩：按照每个学生学号进行分组]order by 排序 [按照总成绩进行排序：sum(成绩)];/*select 学号 ,sum(成绩) from score group by 学号order by sum(成绩) ;

/*【知识点】分组+条件分析思路select 查询结果 [学号, 平均成绩: avg(成绩)]from 从哪张表中查找数据 [成绩表score]where 查询条件 [没有]group by 分组 [学号]having 分组条件 [平均成绩大于60分：avg(成绩 ) >60]order by 排序 [没有];/*select 学号 ,avg(成绩) from score group by 学号  having avg(成绩 ) >60

/*【知识点】时间格式转化*/select 学号 ,timestampdiff(month ,出生日期 ,now())/12 from student ;

select * from student where month (出生日期 ) = month(now())+2;

select a.*,b.成绩 from student as a inner join score as b on a.学号 =b.学号 where b.成绩 <60 and b.课程号 =01order by b.成绩 desc;

select a.教师号,a.教师姓名,avg(c.成绩) from  teacher as a inner join course as b on a.教师号= b.教师号inner join score  c on b.课程号= c.课程号group by a.教师姓名order by avg(c.成绩) desc;

select a.姓名,b.成绩 from student as a inner join score as b on a.学号 =b.学号 inner join course c on b.课程号 =c.课程号 where b.成绩  <60 and c.课程名称 ='数学';

select a.姓名,c.课程名称 ,b.成绩 from student as a inner join score as b on a.学号=b.学号inner join course c on b.课程号 =c.课程号 where b.成绩 >70;

select b.姓名,avg(a.成绩),a.学号  from score as ainner join student as b on a.学号 =b.学号 where a.成绩 <60group by a.学号 having count(a.学号 ) >=2;

select distinct a.学号 ,a.成绩 ,a.课程号 from score as a inner join score as b on a.学号 =b.学号 where a.成绩 =b.成绩 and a.课程号 != b.课程号 ;

select a.学号  from (select 学号 ,成绩 from score where 课程号=01) as ainner join (select 学号 ,成绩 from score where 课程号=02) as bon a.学号 =b.学号 inner join student c on c.学号 =a.学号 where a.成绩 >b.成绩 ;

select a.学号  from (select 学号 ,成绩 from score where 课程号=01) as ainner join (select 学号 ,成绩 from score where 课程号=02) as bon a.学号 =b.学号 inner join student c on c.学号 =a.学号 where a.成绩 >b.成绩 ;

select s.学号 ,s.姓名,a.学号 ,b.课程号,c.教师号 ,c.教师姓名from student as s  inner join score as a  on s.学号 =a.学号 inner join  course  b on a.课程号 =b.课程号inner join  teacher c  on b.教师号 = c.教师号where c.教师姓名 ='孟扎扎';

select 姓名 ,学号 from student where 学号 not in (select a.学号 from student as a inner join score as bon a.学号 =b.学号 inner join course as c on b.课程号 =c.课程号 inner join teacher as d on c.教师号 =d.教师号 where d.教师姓名 ='孟扎扎');

select 学号, 姓名 from student where 学号 not in (select 学号 from score where 课程号=(select 课程号 from course  where 教师号 = (select 教师号 from teacher where 教师姓名 ='孟扎扎')));

select a.姓名,b.成绩 from student as a inner join score as b on a.学号=b.学号inner join course as c on b.课程号 =c.课程号 inner join teacher as d on c.教师号 = d.教师号 where d.教师姓名 = '孟扎扎'order by b.成绩 desc limit 1;

select 学号 ,姓名 from student where 学号 in(select distinct(学号) from score where 课程号 in (select 课程号 from score where 学号=0001)) and 学号 !=0001;

select a.学号,avg(a.成绩 ),max(case when b.课程名称  = '数学' then a.成绩 else null end ) as '数学',max(case when b.课程名称  = '语文' then a.成绩 else null end ) as '语文',max(case when b.课程名称  = '英语' then a.成绩 else null end ) as '英语'from score as ainner join course as b on a.课程号 =b.课程号 group by a.学号 ;

select 学号 ,avg(成绩),row_number () over( order by avg(成绩) desc)from scoregroup by 学号  ;

select 课程号 ,row_number () over(partition by 课程号 order by 成绩 )from score ;

select a.课程号 ,b.姓名 ,a.成绩,a.ranking from (select 课程号 ,学号 ,成绩 ,row_number () over(partition by 课程号 order by 成绩 desc) as rankingfrom  score) as a inner join student as b on a.学号 =b.学号 where a.ranking <3 ;

select b.姓名 ,a.课程号 ,a.成绩 from (select 课程号 ,学号 ,成绩 ,row_number () over( partition by 课程号 order by 成绩 desc) as rankingfrom  score ) as a inner join student as b on a.学号 =b.学号 where a.ranking in( 2,3) ;

select b.姓名 ,a.课程号 ,a.成绩 from (select 课程号 ,学号 ,成绩 ,row_number () over( partition by 课程号 order by 成绩 desc) as 'ranking'from  score ) as a inner join student as b on a.学号 =b.学号 where a.ranking <4 ;