select university,difficult_level,count(difficult_level) / count(distinct a.device_id)
from (select device_id,question_practice_detail.question_id,difficult_level
from question_practice_detail,question_detail
where question_practice_detail.question_id = question_detail.question_id) a,
(select *
from user_profile
where university = '山东大学') b
where a.device_id = b.device_id
group by difficult_level
1.将表二答题表、表三难度表,多表连接当临时表,用question_id去除笛卡尔集。连接在一起后,相当于表二后多了一列难度字段。(多表连接后,有用的字段只有三个,device_id,question_id,difficult_level。其中两表共有字段是question_id,需要指定它所属表二、三哪张表,其实都一样,随便指定一个就行)
select device_id,question_practice_detail.question_id,difficult_level
from question_practice_detail,question_detail
where question_practice_detail.question_id = question_detail.question_id) a
2.因为要找的是山东大学的,所以对表一直接where筛选即可,筛选后查询结果也当一张子查询临时表(结尾的b意思是对这张表取别名为b,省略了as,上面1的a同理)。
(select *
from user_profile
where university = '山东大学') b
3.再对1、2获取的两张临时表进行连接,用device_id去除笛卡尔集。连接后的每一行含义则是山东大学的学生答了哪些题,对应难度是什么。(总览整张表,device_id————每个人自然也会重复出现)
from (select device_id,question_practice_detail.question_id,difficult_level
from question_practice_detail,question_detail
where question_practice_detail.question_id = question_detail.question_id) a,
(select *
from user_profile
where university = '山东大学') b
where a.device_id = b.device_id
4.对最后获取的表按题目难度difficult_level分组,不同难度的划分到一组。用count(difficult_level)计数,则可获取简单,中等,困难每种难度题目的总回答数。而该难度平均答题数 = 该难度总答题数 / 该难度回答人数。 由于最后获取的表device_id会重复,所以对device_id计数时要去重计数。count(distinct a.device_id)。
select university,difficult_level,count(difficult_level) / count(distinct a.device_id)