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

分析：

筛选对象——“当月均完成试卷数”不小于3的用户们

• 当月均完成试卷数——总完成数/月份数（且是有完成情况的月份） Where submit_time is not null Count(exam_id)/count(distinct date_format(start_time,'%Y%m')、
• “当月均完成试卷数”不小于3 新定义的量的筛选应放在having处 Having Count(exam_id)/count(distinct date_format(start_time,'%Y%m')>=3
• 筛选出用户们——Join/where uid in Join：在新join的表中利用having筛选，再借用join把已连接的表进行筛选 Where uid in：In的表一定是单列表，也就是仅有uid一个列

筛选变量——爱作答的类别及作答次数

• 爱作答的类别，此处是把所有用户混在一起 Count(tag)对应的是group by tag，而非group by uid,tag

排序方式——按次数降序输出

• 按次数降序输出——order by tag_cnt desc

通过答案

• 应用join

select tag,count(tag) as tag_cnt
from exam_record as er
join examination_info as ei
on ei.exam_id=er.exam_id

join 
(select distinct uid,
 count(exam_id)/ count(distinct date_format(start_time,'%Y%m')) as cnt
 from exam_record
 where submit_time is not null
 group by uid
having cnt>=3) q
on er.uid=q.uid

group by tag
order by tag_cnt desc

• 应用where uid in

select tag,count(tag) as tag_cnt
from exam_record as er
join examination_info as ei
on er.exam_id=ei.exam_id

where 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
)

group by tag
order by tag_cnt desc