SQL129 月均完成试卷数不小于3的用户爱作答的类别

 统计出 “当月均完成试卷数”不小于3的用户们爱作答的类别及作答次数，按次数降序输出

一、知识点

1、where和having的区别

where:过滤指定的行，无法与聚合函数一起使用

having：过滤分组，与group by连用，后面可以有聚合函数

2、语句位置

select

from

where

group by

having

order by

二、题目分析

先取出当月完成试卷数 >= 3 的用户uid,取出所有符合条件用户的试卷数据，根据 tag 分组，计算每个tag试卷的 start_time 次数

1、当月均完成试卷数

sum(if(submit_time is null,0,1))>=3

2、取出当月完成试卷数 >= 3 的用户uid

where uid in(
    select distinct uid
    from exam_record as a join examination_info as b on a.exam_id=b.exam_id
    group by uid,month(submit_time)
    having sum(if(submit_time is null,0,1))>=3
)

3、作答次数

count(start_time) as tag_cnt

4、连接两表

from exam_record as a join examination_info as b on a.exam_id=b.exam_id

5、完整代码

select tag,count(start_time) as tag_cnt
from exam_record as a join examination_info as b on a.exam_id=b.exam_id
where uid in(
    select distinct uid
    from exam_record as a join examination_info as b on a.exam_id=b.exam_id
    group by uid,month(submit_time)
    having sum(if(submit_time is null,0,1))>=3
)
group by tag
order by tag_cnt desc

欢迎探讨