题目一 :SQL23 统计每个学校各难度的用户平均刷题数
链接:统计每个学校各难度的用户平均刷题数_牛客题霸_牛客网 (nowcoder.com)
问题分解:
- 限定条件:无;
- 每个学校:按学校分组
group by university
- 不同难度:按难度分组
group by difficult_level
- 平均答题数:总答题数除以总人数
count(qpd.question_id) / count(distinct qpd.device_id)
- 来自上面信息三个表,需要联表,up与qpd用device_id连接,qd与qpd用question_id连接
问题解答(完整代码):
select university,
qd.difficult_level,
count(q.question_id) / count(distinct u.device_id) as avg_answer_cnt
from user_profile as u
inner join question_practice_detail as q on u.device_id = q.device_id
inner join question_detail as qd on qd.question_id = q.question_id
group by university,difficult_level;
题目二:SQL24 统计每个用户的平均刷题数
链接:统计每个用户的平均刷题数_牛客题霸_牛客网 (nowcoder.com)
问题分解:
- 限定条件:山东大学;
- 不同难度:按难度分组
group by difficult_level
- 平均答题数:总答题数除以总人数
count(qpd.question_id) / count(distinct qpd.device_id)
- 来自上面信息三个表,需要联表,up与qpd用device_id连接,qd与qpd用question_id连接
问题解答(完整代码) :
select
university,
difficult_level,
count(qpd.question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from user_profile as up
inner join question_practice_detail as qpd
on up.device_id=qpd.device_id and up.university="山东大学"
inner join question_detail as qd
on qd.question_id=qpd.question_id
group by difficult_level
题目三:SQL25 查找山东大学或者性别为男生的信息
链接:查找山东大学或者性别为男生的信息_牛客题霸_牛客网 (nowcoder.com)
问题分解:
利用UNION ALL 进行处理,其中它是默认去重的
问题解答(完整代码) :
select device_id , gender , age , gpa from user_profile where university = '山东大学'
UNION ALL
select device_id , gender , age , gpa from user_profile where gender = 'male'