MYSQL 5.0某表字段及数据大体如下:
monthinfo--------valueinfo
2006-1--------------1
2006-2--------------2
2006-3--------------3
2006-4--------------4
2006-5--------------5
2006-6--------------6
2006-7--------------1
2006-8--------------2
2006-9--------------5
2006-10-------------3查处每个月与之前2个月的总值是多少
2006-10与之前2个月的总值就是3+5+2=10
2006-19与之前2个月的总值就是5+2+1=8
······
·····
预计查询结果如下:
monthinfo--------valueinfo
2006-1--------------1-------null
2006-2--------------2-------null
2006-3--------------3-------6
2006-4--------------4--------9
2006-5--------------5--------12
2006-6--------------6---------15
2006-7--------------1---------12
2006-8--------------2---------9
2006-9--------------5---------8
2006-10-------------3---------10
monthinfo--------valueinfo
2006-1--------------1
2006-2--------------2
2006-3--------------3
2006-4--------------4
2006-5--------------5
2006-6--------------6
2006-7--------------1
2006-8--------------2
2006-9--------------5
2006-10-------------3查处每个月与之前2个月的总值是多少
2006-10与之前2个月的总值就是3+5+2=10
2006-19与之前2个月的总值就是5+2+1=8
······
·····
预计查询结果如下:
monthinfo--------valueinfo
2006-1--------------1-------null
2006-2--------------2-------null
2006-3--------------3-------6
2006-4--------------4--------9
2006-5--------------5--------12
2006-6--------------6---------15
2006-7--------------1---------12
2006-8--------------2---------9
2006-9--------------5---------8
2006-10-------------3---------10
主select 2次的left值加起来 over
")select *,
(select sum(valueinfo) from 表名 b
where cast(concat(b.monthinfo,'-1') as datetime)
between date_add(cast(concat(a.monthinfo,'-1') as datetime),interval -2 month)
and cast(concat(a.monthinfo,'-1') as datetime)
) as total
from from 表名 a
a.valueinfo+(SELECT SUM(b.valueinfo) FROM tth b WHERE a.monthinfo-INTERVAL 2 MONTH=monthinfo)+
(SELECT SUM(b.valueinfo) FROM tth b WHERE a.monthinfo-INTERVAL 1 MONTH=monthinfo)
FROM tth a
当然你也可以用 left join 然后sum