题解 | #月总刷题数和日均刷题数#
月总刷题数和日均刷题数
http://www.nowcoder.com/practice/f6b4770f453d4163acc419e3d19e6746
(select date_format(submit_time,'%Y%m') submit_month,
count(id) month_q_cnt,
round(count(id)/31,3) avg_day_q_cnt
from practice_record
where year(submit_time)=2021 and month(submit_time)=7
group by date_format(submit_time,'%Y%m'))
union
(select date_format(submit_time,'%Y%m') submit_month,
count(id) month_q_cnt,
round(count(id)/31,3) avg_day_q_cnt
from practice_record
where year(submit_time)=2021 and month(submit_time)=8
group by date_format(submit_time,'%Y%m'))
union
(select date_format(submit_time,'%Y%m') submit_month,
count(id) month_q_cnt,
round(count(id)/30,3) avg_day_q_cnt
from practice_record
where year(submit_time)=2021 and month(submit_time)=9
group by date_format(submit_time,'%Y%m'))
union
(select '2021汇总' submit_month,
count(id) month_q_cnt,
round(count(id)/31,3) avg_day_q_cnt
from practice_record
where year(submit_time)=2021
group by date_format(submit_time,'%Y'))
count(id) month_q_cnt,
round(count(id)/31,3) avg_day_q_cnt
from practice_record
where year(submit_time)=2021 and month(submit_time)=7
group by date_format(submit_time,'%Y%m'))
union
(select date_format(submit_time,'%Y%m') submit_month,
count(id) month_q_cnt,
round(count(id)/31,3) avg_day_q_cnt
from practice_record
where year(submit_time)=2021 and month(submit_time)=8
group by date_format(submit_time,'%Y%m'))
union
(select date_format(submit_time,'%Y%m') submit_month,
count(id) month_q_cnt,
round(count(id)/30,3) avg_day_q_cnt
from practice_record
where year(submit_time)=2021 and month(submit_time)=9
group by date_format(submit_time,'%Y%m'))
union
(select '2021汇总' submit_month,
count(id) month_q_cnt,
round(count(id)/31,3) avg_day_q_cnt
from practice_record
where year(submit_time)=2021
group by date_format(submit_time,'%Y'))