题解 | #分组过滤练习题#
分组过滤练习题
http://www.nowcoder.com/practice/ddbcedcd9600403296038ee44a172f2d
两种方法
- 使用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