首先我们先把昵称以『牛客』开头『号』结尾且成就值在1200~2500之间用户筛选出来
select * from user_info where nick_name like '牛客%%号' and achievement between 1200 and 2500再把最近一次活跃(答题或作答试卷)在2021年9月的用户筛选出来。这里用union函数连接
select uid from exam_record group by uid having max(date_format(start_time,'%Y%m'))='202109' union select uid from practice_record group by uid having max(date_format(submit_time,'%Y%m'))='202109'
最后将两个表连接即可,完整代码如下
select t1.uid,t1.nick_name,t1.achievement from (select * from user_info where nick_name like '牛客%%号' and achievement between 1200 and 2500 ) t1 join (select uid from exam_record group by uid having max(date_format(start_time,'%Y%m'))='202109' union select uid from practice_record group by uid having max(date_format(submit_time,'%Y%m'))='202109' ) t2 on t1.uid=t2.uid