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

简单说一下吧，我看了发的解题思路也讲解的很详细，但是都有一个关键错误 SQL_ERROR_INFO: "Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'practice_record.submit_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

该错误很多人并未解决，关键在于分组的错误，因为对月份进行分组之后

day(last_day(submit_time))

该部分同样是分组的结果，很多人并没有分析出来，因此需要对该分组的结果进行取均值，也就是该月的天数，最终结果就出来了！

replace(left(submit_time,7),'-','') as submit_month,
count(*) as month_q_cnt,
round(count(*) / avg(day(last_day(submit_time))) ,3) as avg_day_q_cnt
from practice_record where score is not null 
and year(submit_time) = '2021'
group by replace(left(submit_time,7),'-','')
union all 
select 
'2021汇总' as submit_month,
count(*) as month_q_cnt,
round(count(*) /max(day(last_day(submit_time))) ,3) as avg_day_q_cnt -- /30 会不通过用例
from practice_record where score is not null 
and year(submit_time) = '2021' 
order by submit_month ;