join临时表 的方法 select a.university,avg(b.id_count) as avg_answer_cnt from user_profile a join (select device_id,count(id) as id_count from question_practice_detail group by device_id) b on a.device_id = b.device_id group by a.university order by a.university