1. 通过两次左连接拿到所有答题记录的数据列
  2. 然后通过学校和题目难度进行分组
  3. count(question_id)计算出各学校答题总数，count( distinct device_id) 计算出各学校不同的学生总数，二者相除得到各学校的平均答题数 select university,difficult_level,count(question_id)/count( distinct device_id) as avg_answer_cnt from ( select A.device_id,A.question_id,A.university,q.difficult_level from (select d.device_id,d.question_id,u.university from question_practice_detail d LEFT JOIN user_profile u ON d.device_id = u.device_id )A left join question_detail q on A.question_id = q.question_id )B group by university,difficult_level