select uni as university,round(count(que)/count(distinct dev),4) as avg_answer_cnt from
(
select u.device_id as dev,u.university as uni,question_id as que from user_profile as u
join
(
select device_id,question_id from question_practice_detail
) as q
on u.device_id=t.q.device_id
) as t
group by uni
order by uni

服了