累加
先上表结构:
CREATE TABLE `abc` (
`jidu` int(11) NOT NULL AUTO_INCREMENT,
`jine` int(11) DEFAULT NULL,
PRIMARY KEY (`jidu`)
) ENGINE=InnoDB AUTO_INCREMENT=14270 DEFAULT CHARSET=utf8;
数据:
INSERT INTO `abc` (`jidu`, `jine`) VALUES ('1', '100');
INSERT INTO `abc` (`jidu`, `jine`) VALUES ('2', '200');
INSERT INTO `abc` (`jidu`, `jine`) VALUES ('3', '300');
INSERT INTO `abc` (`jidu`, `jine`) VALUES ('4', '300');
想要的结果为季度金额的累加值:
这里利用错位自关联来实现,先看关联效果
select * from abc a JOIN abc b ON a.jidu >= b.jidu;
下面就好办了,实现语句为:
select a.jidu,a.jine,sum(b.jine) as leiji
from abc a JOIN abc b ON a.jidu >= b.jidu
GROUP BY a.jidu ORDER BY jidu;
累减
还是上面那个表,要的结果为每季度的金额差额:
还是自关联,先看关联效果:
select * from abc a left JOIN abc b ON a.jidu = b.jidu+1 ORDER BY a.jidu;
实现的sql:
select a.jidu,a.jine,ifnull(a.jine-b.jine,a.jine) as cha
from abc a left JOIN abc b ON a.jidu = b.jidu+1 ORDER BY a.jidu;
这就是微学网-程序员之家为你提供的"mysql累加、累减"希望对你有所帮助.本文来自网络,转载请注明出处:http://www.weixuecn.cn/article/7924.html