计算近几周数据然后汇总，sql问题

Question

需求如图

get_water 为饮水量，没有的周数用0填充，如图，上一周没有任何数据

Answer 1

mysql 8.0以上版本,可以使用with递归补充中间缺失的数据。
但有点恶心的是mysql这个recursive只能放在第一个with后面,所以后面的统计每周的汇总sql只能一模一样写两次了

create table t_user_consume_log(
get_water DECIMAL(20,2),remark VARCHAR(200),flag TINYINT(1),
create_time datetime
);
insert into t_user_consume_log values (2600,'',1,now());
insert into t_user_consume_log values (2600,'',1,date_add(now(),interval -1 day));
insert into t_user_consume_log values (2600,'',1,date_add(now(),interval -7 day));
insert into t_user_consume_log values (2600,'',1,date_add(now(),interval -20 day));

with
recursive cte as (
select 0 wd,ifnull((select s from (
select 
WEEK( now() )- WEEK( create_time ) wd,
sum(get_water) s
from t_user_consume_log 
group by WEEK( now() )- WEEK( create_time )) d where wd=0),0) s 
union all
select cte.wd+1,ifnull(d.s,0) from cte left join (
select 
WEEK( now() )- WEEK( create_time ) wd,
sum(get_water) s
from t_user_consume_log 
group by WEEK( now() )- WEEK( create_time )) d on cte.wd+1=d.wd
where cte.wd<=11
)
select concat('近',wd+1,'周') 时间,
'17500ml' 目标饮水量,
concat(s,'ml') 实际饮水量,
concat(round(s/17500*100,2),'%') 完成进度,
case when s>=17500 then '达标' else '不达标' end 是否达标
from cte

---

如果是mysql8.0以下,可以构造一个虚拟的列(可以用系统表),然后left join 上面汇总好的数据

select concat('近',help_topic_id+1,'周') 时间,
'17500ml' 目标饮水量,
concat(ifnull(s,0),'ml') 实际饮水量,
concat(round(ifnull(s,0)/17500*100,2),'%') 完成进度,
case when s>=17500 then '达标' else '不达标' end 是否达标
from mysql.help_topic a left join (select 
WEEK( now() )- WEEK( create_time ) wd,
sum(get_water) s
from t_user_consume_log 
group by WEEK( now() )- WEEK( create_time )) b 
on b.wd=a.help_topic_id
where help_topic_id<=13