mysql 按条件分组累计求和

select (select avg(sales) from 表名 b where (a.date-b.date) between 0 and 20) as [前20日平均销量],* from 表名 a

换其他数据库一个 sum() over (partition by ... )就解决了..

mysql比较麻烦, 可以试下

select a.Daytime,sum(new_Role)

from (select distinct DATE_FORMAT(createtime,'%Y-%m-%d') as Daytime)

from `actors` WHERE ( (`createtime` >= '2019-02-01 00:00:00') AND (`createtime` <= '2019-02-11 24:00:00') )

) a

left join (select * from `actors`

WHERE ( (`createtime` >= '2019-02-01 00:00:00') AND (`createtime` <= '2019-02-11 24:00:00') )

)b

on a.Daytime>=b.createtime

group by a.Daytime

order by 1

创建表：

create table test

(date datetime,

userid varchar(2),

amount int)

insert into test values ('2018-10-02 00:00:00','u1',20)

insert into test values ('2018-10-02 21:09:28','u2',20)

insert into test values ('2018-10-02 21:09:29','u3',30)

insert into test values ('2018-10-03 21:36:44','u1',20)

insert into test values ('2018-10-03 21:36:46','u2',72)

insert into test values ('2018-10-03 21:36:52','u3',30)

insert into test values ('2018-10-30 21:36:54','u1',10)

insert into test values ('2018-10-30 21:36:57','u2',10)

执行：

select a.date,a.userid,a.amount,sum(b.amount) from test a,test b where a.userid=b.userid and a.date>=b.date group by a.date,a.userid,a.amount

结果：

---