SQL刷题 牛客23

SQL1

top 2 -->结尾处limit 2

SQL13

 in('1','3','5') 找的内容不连续

  between 1 and 5 包含首尾，找的内容连续

  is/is not NULL

SQL18

select gender,university,count(device_id) user_num,round(avg(active_days_within_30),1) avg_active_day,round(avg(question_cnt),1) avg_question_cnt 
from user_profile 
group by gender,university //按照性别和学校分组
（round,保留小数）

相关用法：
select * 
from table
where 过滤条件
group by 分组条件
having 筛选特定分组
order by 排序
limit

 SQL19

select university,avg(question_cnt) avg_question_cnt,avg(answer_cnt) avg_answer_cnt 
from user_profile group by university 
having avg_question_cnt<5 or avg_answer_cnt<20

SQL20

select university,avg(question_cnt) avg_question_cnt 
from user_profile 
group by university 
order by avg_question_cnt

 SQL22

//某学校用户平均答题数量计算方式：该学校用户答题总次数除以答过题的不同用户个数
select up.university,count(question_id)/count(distinct qp.device_id) avg_answer_cnt 
from user_profile as up,question_practice_detail as qp 
where qp.device_id=up.device_id 
group by up.university 
order by university

select university,count(question_id)/count(distinct qp.device_id) avg_answer_cnt
from question_practice_detail as qp
inner join user_profile as up
on qp.device_id=up.device_id
group by university
order by university

SQL23

//计算一些参加了答题的不同学校、不同难度的用户平均答题量
select university,difficult_level,count(qp.question_id)/count(distinct qp.device_id) avg_answer_cnt 
from user_profile as up,question_practice_detail as qp,question_detail as qd
where qp.device_id=up.device_id and qp.question_id=qd.question_id
group by university,difficult_level