MySQL ： 求问怎么计算某个月产生过gmv的用户在接下来2个月的产生的gmv

问题在图里，有劳大佬。。。

select a.user_id,to_char(a.date,'yyyyMM'),to_char(b.date,'yyyyMM'),sum(b.gmv) from tableA a join tableA b on a.user_id=b.user_id and （to_char(a.date,'yyyyMM')=to_char(add_month(b.date,1),'yyyyMM')

or to_char(a.date,'yyyyMM')=to_char(add_month(b.date,2),'yyyyMM') ） where a.gmv is not null and a.user_id is not null and b.user_id is not null
group by a.user_id,to_char(a.date,'yyyyMM'),to_char(b.date,'yyyyMM')
应该差不多就是这样，有问题可以稍微改一下

select a.user_id,a.user.type ,(select sum(gmv) om tableA b where b.date>a.date and b.gmv is not null order by b.date desc limit 2 ) amvSum from tableA a where a.gmv is not null。
大致思路是先获取所有不为null的用户，而后利用子查询就行计算gmv的sum。

SELECT CONCAT(YEAR(t1.c_date),'年',MONTH(t1.c_date),'月'),t1.user_type,IFNULL(t2.ct,0)+IFNULL(t3.ct,0) FROM
(SELECT DATE_FORMAT(date,'%Y-%m-01') c_date,user_type,SUM(gmv) ct FROM 表名 GROUP BY 1,2) t1
LEFT JOIN
(SELECT DATE_FORMAT(date,'%Y-%m-01') c_date,user_type,SUM(gmv) ct FROM 表名 GROUP BY 1,2) t2
ON
t1.c_date=DATE_ADD(t2.c_date,INTERVAL -1 MONTH) AND t1.user_type=t2.user_type
LEFT JOIN
(SELECT DATE_FORMAT(date,'%Y-%m-01') c_date,user_type,SUM(gmv) ct FROM 表名 GROUP BY 1,2) t3
ON
t1.c_date=DATE_ADD(t3.c_date,INTERVAL -2 MONTH) AND t1.user_type=t3.user_type
;