题解 | #每个6/7级用户活跃情况#

select uid,
count(distinct date_format(start_time,'%Y%m')) as act_month_total,
count(distinct if(year(start_time)=2021,date_format(start_time,'%Y%m%d'),null)) as act_days_2021,
这个一直不会表示 借鉴了评论区才想到了给下面两个加标签 
count(distinct if(year(start_time)=2021 and tag='exam',date_format(start_time,'%Y%m%d'),null)) as act_days_2021_exam,
count(distinct if(year(start_time)=2021 and tag='practice',date_format(start_time,'%Y%m%d'),null)) as act_days_2021_question
from
(
    select uid,start_time,'exam' as tag
    from exam_record
    union
    select uid,submit_time,'practice' as tag
    from practice_record
) as t1
right join user_info using(uid)
这里用过where uid in ()和left join 结果1003那个都是不出现 后来知道要以ui表为依托 才会出现4个0 
where level=6 or level=7
group by uid
order by act_month_total desc,act_days_2021 desc
随笔记录之 今天竟然在学校图书馆见到了一个小学生 真服了  为什么要放没有一卡通的人进来