这些SQL技能你都会吗? 经典SQL面试题送给你(附答案)

select s_id,sum(score),avg(score),max(score),min(score) from sc where s_id=“01”;

select * from stu where s_name like “%风%”;

select s_id,avg(score) as 分数 from sc

group by s_id

order by 分数 desc,s_id;

select s_id from sc

where score>=60

group by s_id

having count(*)>1;

select s_id from sc

group by s_id

having sum(c_id=“01”)=1 and sum(c_id=“02”)=1 and count(c_id)=2 ;

#方法1

select * from sc sc1 where

(select sum(score>sc1.score) from sc where c_id=sc1.c_id) in(1,2);

#方法2 
使用开窗函数
select * from

(select *,dense_rank() over(partition by c_id order by score desc) 排名 from sc) t

where 排名<=2;

select stu.* from stu

left join sc on stu.s_id=sc.s_id

left join co on sc.c_id=co.c_id

left join te on co.t_id=te.t_id

group by stu.s_id

having ifnull(group_concat(t_name),0) not like “%张三%”;

select stu.*,
sum((c_id=“01”)*score) 01分数,
sum((c_id=“02”)score) 02分数

from stu left join sc on stu.s_id=sc.s_id

group by stu.s_id

having (01分数>02分数) * (sum(c_id in (“01”,“02”))=2);

select s_id,sum(score) 总成绩,
dense_rank() over(order by sum(score) desc) 排名  #开窗函数

from sc

group by s_id;