题解 | #月总刷题数和日均刷题数#
月总刷题数和日均刷题数
http://www.nowcoder.com/practice/f6b4770f453d4163acc419e3d19e6746
简单说一下吧,我看了发的解题思路也讲解的很详细,但是都有一个关键错误 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 ;