题解 | #分组过滤练习题#

两种方法

• 使用having过滤函数联合查询结果
• 使用子查询

# select university,avg(question_cnt) as avg_question_cnt,avg(answer_cnt) as avg_answer_cnt from user_profile
# group by university
# having avg(question_cnt)<5 or avg(answer_cnt)<20;

## 使用子查询
SELECT sp.university,sp.avg_question_cnt,sp.avg_answer_cnt FROM
(SELECT university,avg(question_cnt) as avg_question_cnt,
avg(answer_cnt) as avg_answer_cnt from user_profile
group by university) sp where 
sp.avg_question_cnt<5 or sp.avg_answer_cnt<20;

# select sp.university ,sp.avg_question,sp.avg_answer_cont
# from
# (select university,avg(question_cnt) as avg_question,
# avg(answer_cnt) as avg_answer_cont
# from user_profile
# group by university ) sp
# where sp.avg_question<5
# or sp.avg_answer_cont<20