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

月总刷题数和日均刷题数

https://www.nowcoder.com/practice/f6b4770f453d4163acc419e3d19e6746

select submit_month,month_q_cnt,
case
    when month(submit_month) in (1,3,5,7,8,10,12) then round(month_q_cnt/31,3)
    when month(submit_month) in (4,6,9,11) then round(month_q_cnt/30,3)
    when submit_month='2021汇总' then round(month_q_cnt/31,3)
    else 
    CASE
        WHEN YEAR(submit_month) % 4 = 0 AND (YEAR(submit_month) % 100 != 0 OR YEAR(submit_month) % 400 = 0) THEN round(month_q_cnt/29,3)
        ELSE round(month_q_cnt/28,3)
    END
END AS avg_day_q_cnt
from
(select 
date_format(submit_time,'%Y%m') submit_month,count(*) month_q_cnt
from practice_record 
where year(submit_time) ='2021'
group by submit_month 
union
select '2021汇总' as submit_month,count(*) as month_q_cnt from practice_record
) tbs
order by avg_day_q_cnt;

#拿到这道题，先是把第一列和第二列搞出来

主要用到date_format(submit_time,'%Y%m')格式化函数date_format(日期列,参数)

在 MySQL 中，DATE_FORMAT() 函数用于将日期转换为指定格式的字符串。你可以使用不同的格式代码来定义想要显示的日期格式。以下是一些常见的格式代码： %Y：四位数的年份（例如：2022）； %y：两位数的年份（例如：22）； %m：两位数的月份（01-12）； %c：没有前导零的月份（1-12）； %d：两位数的日期（01-31）； %H：24小时制的小时数（00-23）； %h：12小时制的小时数（01-12）； %i：两位数的分钟数（00-59）； %s：两位数的秒数（00-59）； %p：AM 或 PM； %W：星期的英文全名（例如：Sunday）； %a：星期的英文缩写（例如：Sun）； %M：月份的英文全名（例如：January）； %b：月份的英文缩写（例如：Jan）。

之后就是拼接union 和分类汇总函数group by

select 
date_format(submit_time,'%Y%m') submit_month,count(*) month_q_cnt
from practice_record 
where year(submit_time) ='2021'
group by submit_month 
union
select '2021汇总' as submit_month,count(*) as month_q_cnt from practice_record

第二步是希望根据月份来推荐相应的月数从而计算相应的值

用case when 进行分月处理，最后保留三位小数

这里有两种数据保留类型

不四舍五入的：format(month_q_cnt/30,3)

四舍五入的：round(month_q_cnt/30,3)

case
    when month(submit_month) in (1,3,5,7,8,10,12) then round(month_q_cnt/31,3)
    when month(submit_month) in (4,6,9,11) then round(month_q_cnt/30,3)
    when submit_month='2021汇总' then round(month_q_cnt/31,3)
    else 
    CASE
        WHEN YEAR(submit_month) % 4 = 0 AND (YEAR(submit_month) % 100 != 0 OR YEAR(submit_month) % 400 = 0) THEN round(month_q_cnt/29,3)
        ELSE round(month_q_cnt/28,3)
    END
END AS avg_day_q_cnt

最后就会发现搞不出来，真好

数据还不对，除都能除错。

查了一下数据格式问题

select submit_month,month_q_cnt,
case
    when substring(cast(submit_month as unsigned),5,2) in (01,03,05,07,08,10,12) then round(month_q_cnt/31,3)
    when substring(cast(submit_month as unsigned),5,2) in (04,06,09,11) then round(month_q_cnt/30,3)
    when submit_month='2021汇总' then round(month_q_cnt/31,3)
    else 
    CASE
        WHEN substring(cast(submit_month as unsigned),1,4) % 4 = 0 AND (substring(cast(submit_month as unsigned),1,4) % 100 != 0 OR substring(cast(submit_month as unsigned),1,4) % 400 = 0) THEN round(month_q_cnt/29,3)
        ELSE round(month_q_cnt/28,3)
    END
END AS avg_day_q_cnt
from
(select 
date_format(submit_time,'%Y%m') submit_month,count(*) month_q_cnt
from practice_record 
where year(submit_time) ='2021'
group by submit_month 
union
select '2021汇总' as submit_month,count(*) as month_q_cnt from practice_record
where year(submit_time) ='2021'
) tbs
order by avg_day_q_cnt;