SQL⾯试题讲

SQL⾯试题讲

1.⽤⼀条SQL语句查询出每⻔课都⼤于80分的学⽣姓名

select name
from mst_stu
group by name
having min(score)>80;

2.查询后⼀天 temperature ⽐前⼀天⾼的date
查找与之前（昨天的）⽇期相⽐温度更⾼的所有⽇期的 Id。

select m1.id
from mst_weather m1 join mst_weather m2
on datediff(m1.date,m2.date)=1 and m1.temperature>m2.temperature; 

3.查询每个主播的最⼤level以及对应的最⼩gap(注意:不是每个主播的最⼤level和最⼩gap)

select zhubo_id,level,min(gap)
from mst_zhubo
where(zhubo_id,level) in (select zhubo_id,max(level) max_level
from mst_zhubo
group by zhubo_id)
group by zhubo_id,level;

4.下表是每个课程class_id对应的年级(共有primary、middle、high三个),以及某种⽐率rate

select class_id,
max(case when grade='primary' then rate else 0 end) 'primary',
max(case when grade='middle' then rate else 0 end) 'middle',
max(case when grade='high' then rate else 0 end) 'high'
from mst_class
group by class_id;

或者

select class_id,
max(if(grade='primary',rate,0)) 'primary',
max(if(grade='middle',rate,0))  'middle',
max(if(grade='high',rate,0))  'high'
from mst_class
group by class_id;

5.有两个表A和B，均有key和value两个字段，如果B的key在A中也有，就把B的value换为A中对应的value
这道题的SQL语句怎么写？

update mst_b,(select mst_a.id,mst_a.name
from mst_a join mst_b on mst_a.id=mst_b.id ) T 
set mst_b.name=T.name 
where mst_b.id=T.id;