题解 129 | #月均完成试卷数不小于3的用户爱作答的类#

【分类】：嵌套子查询、多表连接

分析思路

难点：

1.统计出“当月均完成试卷数”不小于3的用户们，with子查询和from子查询一定要去重,in子查询不用去重

2.多表做连接

（1）统计出“当月均完成试卷数”不小于3的用户们

• [条件]：where submit_time is not null

• [使用]：distinct。一定要去重，要不然做连接之后作答次数会计算错误。in不用

（2）统计用户们爱作答的类别及作答次数，按次数降序输出

• [条件]：where a.answer_cnt <3 and upper(a.tag)!= a.tag。

• [使用]：多表连接使用 join using( )

最终结果

select 查询结果 [爱作答的类别;作答次数]
from 从哪张表中查询数据[多个join连接的表]
group by 分组条件 [类别]
order by 对查询结果排序 [按次数降序];

扩展

前往查看：MySQL 嵌套子查询 with子句 from子查询 in子查询 join子查询

求解代码

方法一：

with 子查询

with
    main as(
        #统计出“当月均完成试卷数”不小于3的用户们
        select distinct
            uid
        from exam_record
        join examination_info using(exam_id)
        where submit_time is not null
        group by uid,month(start_time) having count(score)>=3
    )
#统计用户们爱作答的类别及作答次数，按次数降序输出
select
    tag,
    count(start_time) as tag_cnt
from main
join exam_record using(uid)
join examination_info using(exam_id)
group by tag
order by tag_cnt desc

方法二

from 子查询

select 
    tag,
    count(start_time) as tag_cnt
from (
    #统计出“当月均完成试卷数”不小于3的用户们
    select distinct
        uid
    from exam_record
    join examination_info using(exam_id)
    where submit_time is not null
    group by uid,month(start_time) having count(score)>=3
) main
join exam_record using(uid)
join examination_info using(exam_id)
group by tag
order by tag_cnt desc

方法三

in 子查询

select 
    tag,
    count(start_time) as tag_cnt
from exam_record
join examination_info using(exam_id)
where uid in(
    #统计出“当月均完成试卷数”不小于3的用户们
    select
        uid
    from exam_record
    join examination_info using(exam_id)
    where submit_time is not null
    group by uid,month(start_time) having count(score)>=3
)
group by tag
order by tag_cnt desc

方法四

join 子查询

select 
    tag,
    count(start_time) as tag_cnt
from exam_record
join examination_info using(exam_id)
join (    
    #统计出“当月均完成试卷数”不小于3的用户们
    select
        uid
    from exam_record
    join examination_info using(exam_id)
    where submit_time is not null
    group by uid,month(start_time) having count(score)>=3
    )main using(uid)
group by tag
order by tag_cnt desc

总结

只要理清楚求解思路写成任一种查询方法，都可以迁移到另外方法中。拆分成最小问题，逐个解决，再合并。