#思路:整体思路其实很简单,就是把要求的数分别求出来,在进行相连即可,就是代码长。 #另外:由于活跃的月份数和天数不分试卷还是联系,所以先把试卷表和练习表union all形成表tb1备用。 with tb1 as (select uid, start_time as a, 1 as b from exam_record union all select uid, submit_time as a, 2 as b from practice_record) select uid, if(aa is null,0,aa) aa, if(bb is null,0,bb) bb, if(cc is null,0,cc) cc, if(dd is null,0,dd) dd from #求活跃月份 (select uid, count(distinct date_format(a,'%Y%m')) aa from user_info left join tb1 using(uid) where user_info.level in (6,7) group by uid) as tb2 left join #求2021年活跃天数 (select uid, count(distinct date(a)) bb from user_info left join tb1 using(uid) where user_info.level in (6,7) and year(a)=2021 group by uid) as tb3 using(uid) left join #求2021年试卷作答天数 (select uid, count(distinct date(start_time)) cc from user_info left join exam_record using(uid) where user_info.level in (6,7) and year(start_time)=2021 group by uid) as tb4 using(uid) left join #求2021年练习太天数 (select uid, count(distinct date(submit_time)) dd from user_info left join practice_record using(uid) where user_info.level in (6,7) and year(submit_time)=2021 group by uid) as tb5 using(uid) order by aa desc, bb desc