题解 | #筛选限定昵称成就值活跃日期的用户#

问题：请找到昵称以『牛客』开头『号』结尾、成就值在1200~2500之间，且最近一次活跃（答题或作答试卷）在2021年9月的用户信息。

关键问题1.在于最后一个条件筛选“最近一次活跃（答题或作答试卷）在2021年9月的uid” ，根据常规字面理解用uid或者在试卷作答的表格内最近月份是2109 或者在试卷练习的表格内最近月份是2109 即可。两个或则的代码最外面加一个（）表示先对括号内的或语句进行预算，再和外面的nickname、成就一起限定条件筛选。

如果或语句最外面不加括号，则默认先进行整个条件筛选and的运算，再进行or的运算，这样得到的结果范围就更大，就不对了。

关键问题2.nickname中必须以牛客开头、号结尾，使用like ‘牛客%号’的语句，中间符号“%”表示任意字符。

如果觉得两个or语句写的太麻烦，用一下union的语句逻辑也一样。

这个是传统思维：

SELECT uid, nick_name, achievement FROM user_info 
WHERE nick_name LIKE '牛客%号' AND achievement BETWEEN 1200 AND 2500 
AND (uid IN 
     	(
    	SELECT uid FROM exam_record 
    	GROUP BY uid
    	HAVING MAX(DATE_FORMAT(start_time,'%y%m'))='2109'
    	)  
    OR uid IN
    	(
     	SELECT uid FROM practice_record 
     	GROUP BY uid 
     	HAVING MAX(DATE_FORMAT(submit_time,'%y%m'))='2109'
     	)
     )

这个是union语句（能少写两句话？？）

SELECT uid, nick_name, achievement FROM user_info 
WHERE nick_name LIKE '牛客%号' AND achievement BETWEEN 1200 AND 2500 
AND uid IN (
    	SELECT uid FROM exam_record 
    	GROUP BY uid
    	HAVING MAX(DATE_FORMAT(start_time,'%y%m'))='2109'
    UNION
     	SELECT uid FROM practice_record 
     	GROUP BY uid 
     	HAVING MAX(DATE_FORMAT(submit_time,'%y%m'))='2109'
     )