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
参考资料