多表查询

子查询

select
device_id,
question_id,
result
from
question_practice_detail
where
device_id = (
  select
  device_id
  from
  user_profile
  where
  university = '浙江大学'
)
order by
  question_id asc

select
  question_practice_detail.device_id,
  question_id,
  result
from
  question_practice_detail
  inner join user_profile on question_practice_detail.device_id = user_profile.device_id
where
  user_profile.university = '浙江大学'
order by
  question_id asc

链接查询

统计每个学校的答过题的用户的平均答题数

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

select
  university, round(count(1) / count(distinct(u.device_id)), 4) as avg_answer_cnt
from
  user_profile u,
  question_practice_detail p
where
  p.device_id = u.device_id
group by
  university
order by
  university

统计每个学校各难度的用户平均刷题数

select
  u.university,
  q2.difficult_level,
  round(count(u.device_id) / count(distinct u.device_id), 4) avg_answer_cnt
from
  user_profile u
  join question_practice_detail q on u.device_id = q.device_id
  join question_detail q2 on q2.question_id = q.question_id
group by
  u.university,
  q2.difficult_level
order by
  u.university

select
  university,
  p1.difficult_level,
  round(count(1) / count(distinct(u.device_id)), 4) as avg_answer_cnt
from
  user_profile u,
  question_practice_detail p,
  question_detail p1
where
  p.device_id = u.device_id
  and p1.question_id = p.question_id
group by
  university,
  p1.difficult_level
order by
  university

统计每个用户的平均刷题数

select
  u.university,
  q2.difficult_level,
  round(count(u.device_id) / count(distinct u.device_id), 4) avg_answer_cnt
from
  user_profile u
  join question_practice_detail q on u.device_id = q.device_id
  join question_detail q2 on q2.question_id = q.question_id
group by
  u.university,
  q2.difficult_level
having
  u.university = '山东大学'

select
  university,
  p1.difficult_level,
  round(count(1) / count(distinct(u.device_id)), 4) as avg_answer_cnt
from
  user_profile u,
  question_practice_detail p,
  question_detail p1
where
  p.device_id = u.device_id
  and p1.question_id = p.question_id
group by
  university,
  p1.difficult_level
having
  university = '山东大学'

组合查询

查找山东大学或者性别为男生的信息

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'

条件函数

计算25岁以上和以下的用户数量

select 
(case 
 when age<25 then '25岁以下'
 when age is null  then '25岁以下'
else '25岁及以上' end) as age_cut,count(*) number
from user_profile
group by age_cut

select 
(case 
 when age>=25 then '25岁及以上'
else '25岁以下' end) as age_cut,count(*) number
from user_profile
group by age_cut

查看不同年龄段的用户明细

select device_id,gender,
(case 
 when age<20 then '20岁以下'
 when age>=20 and age<25  then '20-24岁'
  when age>=25  then '25岁及以上'
else '其他' end) as age_cut
from user_profile

日期函数

计算用户8月每天的练题数量

select
  day(date) as day,
  count(1) as question_cnt
from
  question_practice_detail
where
  year(date) = 2021
  and month(date) = 8
group by
  date

select
  day(date) as day,
  count(1) as question_cnt
from
  question_practice_detail
group by
  date
having
  (
    year(date) = 2021
    and month(date) = 8
  )