错题难题丨题目的业务逻辑要弄清楚

业务逻辑要弄清楚，完成次数和作答次数是不一样的

而且select的语句和过滤语句可以不一样，

过滤语句看可以用前面没有出现的、完全没有用过的

SELECT  
uid,
DATE_FORMAT(submit_time,'%Y%m') MONTH,
SUM(IF(submit_time is not NULL,1,0))tag_cnt
FROM exam_record 
WHERE DATE_FORMAT(submit_time,'%Y%m') is not null
GROUP BY uid,MONTH
HAVING SUM(IF(submit_time is not NULL,1,0))>=3;
#月均完成大于等于3套卷的用户们


SELECT 
tag,
SUM(IF(start_time is not NULL,1,0))tag_cnt 
FROM 
exam_record a
LEFT JOIN 
examination_info b
on a.exam_id=b.exam_id
WHERE uid 
in(SELECT uid FROM (SELECT  
													uid,
													DATE_FORMAT(submit_time,'%Y%m') MONTH,
													SUM(IF(start_time is not NULL,1,0))tag_cnt
										FROM exam_record 
										WHERE DATE_FORMAT(submit_time,'%Y%m') is not null
										GROUP BY uid,MONTH
										HAVING SUM(IF(submit_time is not NULL,1,0))>=3)
d)
GROUP BY tag
ORDER BY tag_cnt  desc