由于实际需要,特意写此博客记录一下怎么在mysql中实现累加的功能,达到python中cumsum的效果
博客参考:https://www.cnblogs.com/bourneli/archive/2013/08/09/3248908.html
有如下数据:
实现如下功能1,即新增一列求累计值:
实现如下功能2,即新增一列求当月累计值:
建表与插入数据sql:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for cum_demo建表
-- ----------------------------
DROP TABLE IF EXISTS `cum_demo`;
CREATE TABLE `cum_demo` (
`id` int(11) NOT NULL,
`money` int(11) NULL DEFAULT NULL,
`t` timestamp(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of cum_demo插入记录
-- ----------------------------
INSERT INTO `cum_demo` VALUES (1, 10, '2019-05-31 19:10:45');
INSERT INTO `cum_demo` VALUES (2, 20, '2019-06-01 19:10:59');
INSERT INTO `cum_demo` VALUES (3, 30, '2019-06-01 19:11:03');
INSERT INTO `cum_demo` VALUES (4, 40, '2019-06-02 19:11:37');
SET FOREIGN_KEY_CHECKS = 1;
功能1的实现:
方法一:
SELECT
a.id,
-- lt.id as lid,
a.money as money,
a.t as att,
sum(lt.money) as cur_month_money
-- concat(DATE_FORMAT(a.t,'%Y-%m'),'-01')as t_format,
-- lt.t as ltt
FROM cum_demo a
LEFT JOIN cum_demo lt ON a.t >= lt.t -- on后面是关键
GROUP BY a.id
ORDER BY id
方法二:
SELECT
a.id,
a.money as money,
a.t as att,
(SELECT sum(money) from cum_demo where t<= a.t ) as cum_money
from cum_demo a;
功能2的实现:
方法1:
SELECT
a.id,
-- lt.id as lid,
a.money as money,
a.t as att,
sum(lt.money) as cur_month_money
-- concat(DATE_FORMAT(a.t,'%Y-%m'),'-01')as t_format,
-- lt.t as ltt
FROM cum_demo a
LEFT JOIN cum_demo lt ON a.t >= lt.t and lt.t >= concat(DATE_FORMAT(a.t,'%Y-%m'),'-01')
GROUP BY a.id
ORDER BY id
方法2:
SELECT
a.id,
a.money as money,
a.t as att,
(SELECT sum(money) from cum_demo where t<= a.t and DATE_FORMAT(t,'%Y-%m')=DATE_FORMAT(a.t,'%Y-%m')) as cum_money
from cum_demo a;
归根结底我们会发现,实现功能2和功能1,区别就在于多增加了一个时间比较字段,即and后面的时间字段比较
注:方法2是由某位大神提供,逻辑简单易懂,感谢大神。
问题与疑惑:
在测试的过程中,发生了如下诡异现象:
另外,如果使用concat(t_format,’-01’)将t_format字段其转化成年月日的形式,就可以进行比较,这让我有点难以理解,还望大神指点一二。