通过代码

SELECT
    tag,
    count(*) tag_cnt
FROM
    examination_info e_i
        RIGHT JOIN
    exam_record e_r
    on
            e_r.exam_id = e_i.exam_id
        JOIN(select distinct u.uid
             from (
                      select uid,
                             DATE_FORMAT(submit_time, '%Y%m') month
                      FROM exam_record
                      group by uid, month
                      HAVING COUNT(submit_time) >= 3
                  ) u
    ) u1
            on u1.uid = e_r.uid
GROUP BY
    tag
ORDER BY
    tag_cnt desc

说来这题也挺简单就是先计算出月均完成试卷>2 的uid

然后表连接检查对应exam_id的tag，count(*)就行

遇到的问题

然后我遇到的问题也很离谱

第一个问题就不粘出来了，太丢人

我在join后边跟了一个“{}”，最近一直在看java导致下意识跟了一大括号，这网页在线判题也挺离谱，硬是不报错，就说期望输出不对。我也是笨比一个，半天没看出来啥问题，还以为不能先RIGHTT JOIN再INNER JOIN ，，还是基础不牢，没办法打开idea直接发现错误。。。哎

第二个问题

就是下面这一块

JOIN(select 
        uid,
        DATE_FORMAT(submit_time, "%Y%m") month
     FROM exam_record
     group by month, uid
     HAVING COUNT(submit_time) >= 3
) u
on u.uid = e_r.uid

alt

我这group by了，这样一旦有用户，连续两个月均完成试卷大于2，就会记录两个uid，然后就会统计两次他的题的tag

alt

而我解决办法也是简单粗暴：代码就在上边，我又套了一层select distance完事，然后想想可以用where in 来解决问题

也就是

where uid in(
    select uid
    FROM exam_record
    group by uid, DATE_FORMAT(submit_time, '%Y%m')
    HAVING COUNT(submit_time) >= 3
    )

完美。

旧知识时间

where