sql多表查询之嵌套子查询

题1

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

select 
    tag,
    count(tag) as tag_cnt
from 
    exam_record 
    JOIN
    examination_info  
    using(exam_id)
where 
    # 查询满足：月均完成试卷数不小于3的用户
    uid in
    (select 
         uid
     from 
         exam_record
     where
         submit_time is not null
     group by
         uid
     having count(exam_id) / count(distinct date_format(start_time, "%Y%m")) >= 3
    )
# where筛选之后，按examination_info表的tag进行分组统计，最后降序输出
group by
    tag
order BY
    tag_cnt desc;

或

select 
    b.tag,
    count(tag) as tag_cnt # count(*) tag_cnt
from 
    exam_record as a
    JOIN
    examination_info as b
    using(exam_id)
#     ON a.exam_id = b.exam_id
where 
    # 查询满足：月均完成试卷数不小于3的用户
    a.uid in
    (select 
         uid
     from 
         exam_record
     where
         submit_time is not null
     group by
         uid
     having count(exam_id) / count(distinct date_format(start_time, "%Y%m")) >= 3
    )
# where筛选之后，按examination_info表的tag进行分组统计，最后降序输出
group by
    b.tag
order BY
    tag_cnt desc;

或

SELECT
    b.tag,
    count(*) tag_cnt
FROM
    exam_record a
LEFT JOIN examination_info b ON a.exam_id = b.exam_id
# 查询满足：月均完成试卷数不小于3的用户
WHERE
    a.uid IN (
        SELECT
            uid
        FROM
            exam_record
        WHERE
            submit_time IS NOT NULL
        GROUP BY
            uid,
            DATE_FORMAT(submit_time, '%Y%m')
        HAVING
            COUNT(submit_time) > 2
    )
# 筛选之后，按examination_info表的tag进行分组统计，最后降序输出
GROUP BY
    b.tag
ORDER BY
    tag_cnt DESC

题2

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

法1

select
    exam_id,
    count(distinct uid) as uv,
    round(avg(score), 1) as avg_score
from
    exam_record
where 
    (exam_id, DATE(start_time)) IN 
    (select exam_id, DATE(release_time) from examination_info where tag = "SQL")
     AND 
     uid IN (select uid from user_info where level>5)
group by
    exam_id
ORDER BY 
    uv DESC,
    avg_score ASC;

法2：两次left join表连接：

select 
    exam_id,
    count(distinct ui.uid) as uv,
    round(avg(score),1) as avg_score
from 
    exam_record 
    left join 
    user_info ui
    using(uid)
    left join 
    examination_info 
    using(exam_id)
where 
    tag='SQL'
    and level>5
    and date_format(submit_time,'%Y%m%d')=date_format(release_time,'%Y%m%d')
group by 
    exam_id
order by 
    uv desc,avg_score

题3

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

法1

select 
    level,
    count(distinct u_i.uid) as level_cnt
from 
    exam_record as e_r
    join
    examination_info as e_i
    on e_r.exam_id = e_i.exam_id
    join
    user_info as u_i
    on e_r.uid = u_i.uid
#     using(exam_id)
where 
    tag = 'SQL'
    and 
    score > 80 
group by
    level
order by 
    level_cnt desc,
    level desc

法2：使用in运算符分步查询

第一步：在examination_info表中找出SQL试卷的exam_id
第二步：在exam_record表中根据找到的exam_id找出做了SQL试卷且分数大于80的uid
第三步：在user_info表中根据uid找到相应的用户，使用相关聚合函数完成查询

select
    level,count(uid) level_cnt
from
    user_info
where
    uid in
        (select uid from exam_record where exam_id in # 第二步
            (select exam_id from examination_info where tag='SQL') and score>80) # 第一步
group by
    level
order by
    level_cnt desc  # 第三步