面试常见的sql语句

1. 有筛选条件的统计数量问题的万能模板

select sum(
case when <判断表达式> then 1
       else 0
end
) as 数量
from 信息表；

select 
sum(
    case when 平均成绩>80 then 1
    else 0 end) as 人数,
sum(
    case when 平均成绩>80 then 1
    else 0 end)/count(学号) as 人数占比
from  (
    select 学号,avg(分数) as 平均成绩
    from 成绩表
    group by 学号
) a

参考资料

2、图解sql面试题

https://mp.weixin.qq.com/mp/appmsgalbum?__biz=MzAxMTMwNTMxMQ==&action=getalbum&album_id=1398781984763428865&subscene=159&subscene=189&scenenote=https%3A%2F%2Fmp.weixin.qq.com%2Fs%3F__biz%3DMzAxMTMwNTMxMQ%3D%3D%26mid%3D2649247313%26idx%3D1%26sn%3De3d87d81f7655857f54752c71f67a710%26chksm%3D835fc661b4284f7740433c15404e3e476033ae27494db87eef8036c7571a112472ba3d5d6008%26cur_album_id%3D1398781984763428865%26scene%3D189%23wechat_redirect&nolastread=1#wechat_redirect

参考资料

你会"拉链"吗？

---