题解 | #每个6/7级用户活跃情况#
https://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1
思路：根据要返回的结果以及题目意思，由于试卷表和题目表是两张不同的表，两者并没有联系，但是结果既需要返回试卷表的活跃数也需要返回题目表的活跃数，这里我们分别去查询这两张表，利用union all进行连接
(select u.uid,e.submit_time,1 as type from user_info u left join exam_record e on e.uid=u.uid where u.`level` BETWEEN 6 and 7 union all select u.uid,p.submit_time, 0 as type from user_info u left join practice_record p on p.uid=u.uid where u.`level` BETWEEN 6 and 7)这里查询到的就是所有的对应的信息，为了区别试卷表和题目表数据的不同，我这里定义了一个type用来做区分
接下来我们只需要写子查询，并且分一下组就可以得到答案，sql如下：
select u.uid, count(distinct DATE_FORMAT(u.submit_time,'%Y-%m')) as act_month_total, count(distinct case when year(u.submit_time)=2021 then DATE_FORMAT(u.submit_time,'%Y-%m-%d') end) as act_days_2021, count(distinct case when YEAR(u.submit_time)=2021 and u.type=1 then DATE_FORMAT(u.submit_time,'%Y-%m-%d') end) as act_days_2021_exam, count(distinct case when YEAR(u.submit_time)=2021 and u.type=0 then DATE_FORMAT(u.submit_time,'%Y-%m-%d') end) as act_days_2021_question from (select u.uid,e.submit_time,1 as type from user_info u left join exam_record e on e.uid=u.uid where u.`level` BETWEEN 6 and 7 union all select u.uid,p.submit_time, 0 as type from user_info u left join practice_record p on p.uid=u.uid where u.`level` BETWEEN 6 and 7) as u group by u.uid order by act_month_total desc ,act_days_2021 desc
⑨✌们拿它练练面试愣是给他整出幻觉了
