题解 | #月总刷题数和日均刷题数#

# 使用day(last_day())来计算某月的天数
with t1 as
(select date_format(submit_time,'%Y%m') as submit_month, 
count(*) as month_q_cnt, 
round(count(*)/day(last_day(max(submit_time))),3) as avg_day_q_cnt
from practice_record
where year(submit_time) = '2021'
group by date_format(submit_time, '%Y%m')
order by submit_month)

select * from t1
union
select '2021汇总', sum(month_q_cnt), round(sum(month_q_cnt)/31,3) from t1;

除了day(last_day())函数计算某月的天数

还可以有另一个计算方法：datediff(date_add(submit_time, interval 1 month), submit_time)

这个通过本月某日与下月某日的相差天数来计算本月天数的方法也值得借鉴

另外，union函数在此题需求中也是需要被用来合并查询结果的。