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;