题解 | #月总刷题数和日均刷题数#
月总刷题数和日均刷题数
http://www.nowcoder.com/practice/f6b4770f453d4163acc419e3d19e6746
SELECT DATE_FORMAT(submit_time,'%Y%m') AS submit_month,
COUNT(id) AS month_q_cnt,
ROUND(COUNT(id)/AVG(DAY(LAST_DAY(submit_time))),3) AS avg_day_q_cnt
FROM practice_record
WHERE YEAR(submit_time)=2021
GROUP BY DATE_FORMAT(submit_time,'%Y%m')
UNION
SELECT '2021汇总' AS submit_month,
COUNT(id) AS month_q_cnt,
ROUND(COUNT(id)/31,3) AS avg_day_q_cnt
FROM practice_record
WHERE YEAR(submit_time)=2021
COUNT(id) AS month_q_cnt,
ROUND(COUNT(id)/AVG(DAY(LAST_DAY(submit_time))),3) AS avg_day_q_cnt
FROM practice_record
WHERE YEAR(submit_time)=2021
GROUP BY DATE_FORMAT(submit_time,'%Y%m')
UNION
SELECT '2021汇总' AS submit_month,
COUNT(id) AS month_q_cnt,
ROUND(COUNT(id)/31,3) AS avg_day_q_cnt
FROM practice_record
WHERE YEAR(submit_time)=2021
ORDER BY submit_month
注意：AVG(DAY(LAST_DAY(submit_time))，如果没有AVG会因为GROUP BY 报错。
投递科大讯飞等公司10个岗位 >