题解 | #月总刷题数和日均刷题数#
月总刷题数和日均刷题数
https://www.nowcoder.com/practice/f6b4770f453d4163acc419e3d19e6746
select coalesce(submit_month, '2021汇总'), count(submit_month), round(count(submit_month) / max(alldays), 3) from ( select case when isnull (date_format (submit_time, '%Y%m')) = 0 then date_format (submit_time, '%Y%m') else 0 end as submit_month, dayofmonth (last_day (submit_time)) alldays, count(question_id) qscount from practice_record where year (submit_time) = 2021 group by submit_time ) as newform group by submit_month with rollup
这道题很难主要难点是要把yy-mm-dd格式改成yy-mm,切要对每个月求出最大的月份这个数据在子查询中即可,在主查询中需要根据子表中的yy-mm分类,由于每个月份下的天数是固定的,所以选择max函数即可,但是由于roll up是根据select 全部属性进行一个汇总,但是由于年份系统默认为null,所以需要用coalesce函数去转换成2021年度,整体代码如上。