mysql累加、累减

 

累加

先上表结构:

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;

完事,如有不对的地方请指正。

posted @ 2019-12-31 16:57  王2  阅读(...)  评论(...编辑  收藏