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

注释部分是我写的,不知道为什么还有一个实例通过不了,有懂的友友能指正以下吗
# select
#     tag,
#     count(1) tag_cnt
# from
#     exam_record
#     left join examination_info using (exam_id)
# where
#     uid in (
#         select
#             uid
#         from
#             (
#                 select
#                     uid,
#                     count(1) we
#                 from
#                     exam_record
#                 where
#                     score is not null
#                     and month (start_time) = month (submit_time)
#                 group by
#                     uid
#                 having
#                     we > 2
#             ) t
#     )
# group by
#     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
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
	)
GROUP BY
	b.tag
ORDER BY
	tag_cnt DESC