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

mysql有些操作真的很迷，这个题应该是小问题多，所以通过率很低，大家应该都会

(
    select
        b.month,
        count(1) as month_q_cnt,
        format (count(1) / max(b.day), 3) as avg_day_q_cnt
    from
        (
            select
                a.question_id,
                date_format (a.submit_time, "%Y%m") as month,
                day (last_day (a.submit_time)) as day
            from
                practice_record a
            where
                year (a.submit_time) = 2021
        ) b
    group by
        b.month
    order by
        b.month limit 99999
)
union all
(
    select
        "2021汇总" as submit_month,
        count(1) as month_q_cnt,
        format (count(1) / 31, 3) as avg_day_q_cnt
    from
        practice_record a
    where
        year (a.submit_time) = 2021
)