前记
当我们需要统计累积量的时候就需要计算同一业务以及相邻两行的差值
业务
接下来我们模拟一个业务，就是统计每天数据表的行数增加量。首先我们需要每天将当天表的数据行数写入到数据库
表结构如下：
1
2
3
4
5
6
7
8
9
| CREATE TABLE `table_logs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`table_name` char(64) NOT NULL DEFAULT '_' COMMENT 'table_name',
`row_num` bigint(20) NOT NULL DEFAULT 0 COMMENT '行数',
`created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT '创建时间',
`updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
KEY `uuid` (`uuid`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
|
实现
方法：t1.rownum = t2.rownum - 1
代码：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
| SELECT * FROM
(
SELECT
(@rownum := @rownum + 1) AS rownum,
`id`,
`table_name`,
`row_num`
FROM
table_logs,
(SELECT @rownum := 0) t
WHERE
1 = 1
ORDER BY
`table_name` desc, `id` desc
) as t1
LEFT JOIN (
SELECT
(@INDEX := @INDEX + 1) AS rownum,
`id`,
`table_name`,
`row_num`
FROM
table_logs,
(SELECT @rownum := 0) t
WHERE
1 = 1
ORDER BY
`table_name` desc, `id` desc
) as t2 on t1.rownum = t2.rownum - 1 and t1.table_name = t2.table_name
|
最后只需要将 t1.row_num-t2.row_num 就可以实现我们的需求