sql多表查询之连接查询

题1

https://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

select 
    uid,
    exam_cnt,
    if(question_cnt is null, 0, question_cnt)
from
    (select 
        uid,
        count(submit_time) as exam_cnt
    from exam_record
    where YEAR(submit_time) = 2021
    group by uid) as t

left join

(select
    uid,
    count(submit_time) as question_cnt
from practice_record
where YEAR(submit_time) = 2021
group by uid) as t2 using(uid)

where uid in
    (
    select
        uid
    from exam_record
    join examination_info using(exam_id)
    join user_info using(uid)
    where tag = 'SQL' and difficulty = 'hard' and `level` = 7
    group by uid
    having avg(score) >= 80
    )
order by exam_cnt asc, question_cnt desc

题目2

https://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

select 
    u_i.uid as uid,
       count(distinct act_month) as act_month_total,
       count(distinct case 
             when year(act_time) = 2021 
             then act_day 
             end) as act_days_2021,
       count(distinct case 
             when year(act_time) = 2021 
             and tag = 'exam' 
             then act_day 
             end) as act_days_2021_exam,
        count(distinct case
             when year(act_time) = 2021
             and tag = 'question'
             then act_day
             end) as act_days_2021_question
from 
    user_info as u_i
    left join (select uid,
                 start_time as act_time,
                 date_format(start_time, '%Y%m') as act_month,
                 date_format(start_time, '%Y%m%d') as act_day,
                 'exam' as tag
          from exam_record
          union all 
          select uid,
                 submit_time as act_time,
                 date_format(submit_time, '%Y%m') as act_month,
                 date_format(submit_time, '%Y%m%d') as act_day,
                 'question' as tag
          from  practice_record
          ) as exam_and_practice
    on exam_and_practice.uid = u_i.uid
    
where u_i.level >= 6
group by uid
order by act_month_total desc, act_days_2021 desc