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

代码一: 可以检测通过,但是总是会在第一次报错,重复运行就不会有问题 (其中主要关键点: SELECT中的元素,包括函数使用 在使用了GROUP BY 之后,应该是需要包含或者依赖于这个分组元素的,不然就会于MYSQL 中sql_mode=only_full_group_by 冲突; 因此后续添加了 MAX()函数,而且由于 原表中没有 submit_month,虽然GOROUP　BY也可以引用，但是于DATE_FORMAT(submit_time,'%Y%m')　相比较，其意义不同－－一个是submit_month／submit_time)

select DATE_FORMAT(submit_time,'%Y%m') submit_month,
count(question_id) month_q_cnt,
ROUND(count(question_id)/MAX(day(LAST_DAY(submit_time))),3) avg_day_q_cnt 
from practice_record
where DATE_FORMAT(submit_time,'%Y')='2021'
group by DATE_FORMAT(submit_time,'%Y%m')
union ALL

SELECT '2021汇总' as submit_month,
count(question_id) month_q_cnt,
round(count(id)/31,3) avg_day_q_cnt
from practice_record
where DATE_FORMAT(submit_time,'%Y')='2021'
order by submit_month
;

代码二: 自己运行的时候可以通过,提交代码后总会多出现一行 202108月的统计(计算也不对),然后报错;一直没有找出来问题所在?? 第二个代码的思路是---没有那个指标就创造那个指标然后嵌套使用;

SELECT submit_mounth, COUNT(1) as month_q_cnt, ROUND(COUNT(1)/md,3) as avg_day_q_cnt
FROM 
(
    SELECT DATE_FORMAT(submit_time,'%Y%m') as submit_mounth,  question_id,
           day(last_day(submit_time)) as md
    FROM practice_record
) b
GROUP BY submit_mounth ,md


union ALL

SELECT '2021汇总' as submit_month,
count(question_id) month_q_cnt,
round(count(id)/31,3) avg_day_q_cnt
from practice_record
where DATE_FORMAT(submit_time,'%Y')='2021'
order BY submit_mounth  # 这块排序是为了后面对比的时候比较方便