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

select
    date_format (submit_time, '%Y%m') submit_month,
    count(submit_time) month_q_cnt,
    round(
        count(submit_time) / max(day (last_day (submit_time))),
        3
    ) avg_day_q_cnt
from
    practice_record
where
    year (submit_time) = '2021'
group by
    submit_month
union all
select
    '2021汇总' as submit_month,
    count(submit_time) month_q_cnt,
    round(count(submit_time) / 31, 3) avg_day_q_cnt
from
    practice_record
where
    year (submit_time) = '2021'
order by
    submit_month;

！！！！！惨痛代价！！！！因为在第一个select语句习惯性加了个分号，导致一直报错，找了好久才发现是这个原因

在使用UNION ALL连接多个SELECT语句时，每个SELECT语句之间不需要使用分号进行分隔。这是因为UNION ALL是一个单独的SQL操作符，用于将多个查询结果合并成一个结果集。在使用UNION ALL时，每个SELECT语句应该以自己的完整形式存在，并且它们之间没有语法上的分隔符。

如果在UNION ALL连接时使用了分号，会导致语法错误，因为分号会被解释为一个独立的SQL语句的结束符号，而不是UNION ALL操作符的一部分。

知识点1：

UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

知识点2：

last_day()返回参数日期的最后一天，day(last_day())返回当月的天数

例如：

select last_day('2008-02-01'); -- 2008-02-29

select last_day('2008-08-08'); -- 2008-08-31

MySQL last_day() 函数非常有用，比如我想得到当前月份中有多少天，可以这样来计算：

mysql> select now(), day(last_day(now())) as days;

+---------------------+------+

| now() | days |

+---------------------+------+

| 2008-08-09 11:45:45 | 31 |

ps：这里最容易出错的点在于：每月天数的计算

(1) 计算每个月的天数可以用函数：day(last_day(datetime));

(2)最容易出错的点在于 : group by DATE_FORMAT(submit_time,'%Y%m') 分组后，select后面只能跟：group by 分组字段、常量、以及 count()/ max()/min()/avg()/sum()等聚合函数；

*group by 分组后，select后面只能跟：

(1) groupby 分组的字段;

(2)常量;

(3) count()、 max()、 min()、avg()、sum()等聚合函数；

由于 count(submit_time) / max(day(last_day(submit_time)) 中 分子count(submit_time)用的是聚合函数，分母也必须用聚合函数，而函数day() 不是聚合函数，因此分母最终的逻辑为：max(day(last_day(submit_time)) 或min(day(last_day(submit_time))

第二种解法：利用group by with rollup 替换union all进行简化

group by 子句使用with rollup关键字之后，具有分组加和的功能。即：在所有的分组记录之后，自动新增一条记录，从全局计算所有记录的数据。

再利用ifnull()或coalesce()函数对空字段赋值

coalesce()函数：空字段赋值

语法：coalesce (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值，即返回参数中的第一个非空值，如果所有值都为null，那么直接返回null。

例如：select coalesce（null,'100','50')；输出为100-------第一个非空值。

IFNULL()函数：MySQL控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数。两个参数可以是文字值或表达式。

语法：IFNULL(expression_1,expression_2);

如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果。

------------------------------------------------------------------------------------

select coalesce(months,'2021汇总') as submit_month,

       count(question_id) as month_q_cnt,

       round(count(question_id)/max(days),3) as avg_day_cnt

from(select question_id,

            date_format(submit_time,'%Y%m') as months,

            day(last_day(submit_time)) as days

    from practice_record

    where year(submit_time)= '2021')  tmp1

group by months

with rollup;

----------------------------------------------------------------------------------------

详情见：

https://blog.csdn.net/SHWAITME/article/details/136077037?ops_request_misc=&request_id=&biz_id=102&utm_term=MAX(DAY(last_day(submit_time))&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduweb~default-1-136077037.142^v100^pc_search_result_base2&spm=1018.2226.3001.4187

！！！！！！！错误代码：

select

    coalesce(date_format (submit_time, '%Y%m'),'2021汇总') submit_month,

    count(submit_time) month_q_cnt,

    round(

        count(submit_time) / max(day (last_day (submit_time))),

        3

    ) avg_day_q_cnt

from

    practice_record

where

    year (submit_time) = '2021'

group by

    submit_month

with rollup;

预期输出：

实际输出：

说明 coalesce(date_format (submit_time, '%Y%m'),'2021汇总')并没有起到作用，个人理解：date_format (submit_time, '%Y%m')不会出现null的情况，所以语句无效，因为group by 子句使用with rollup关键字之后，在所有的分组记录之后，自动新增一条记录none记录，此时none记录的字段应当是submit_month，因此应当是先以submit_month字段汇总产生none，再用coalesce()函数去none值。