mysql - 比较 2 个 MySQL 行并计算增量

Question

我有一个包含一些统计信息的表格，选择和分组后如下所示:

SELECT * FROM Statistics WHERE id IN (
    select max(id) id
    from Statistics
    group by DAY(Statistics.Datetime_Scan)
)

ID      Datetime_Scan   Count
2       4/6/2017        302
10      4/7/2017        391
18      4/8/2017        500
26      4/9/2017        605
34      4/10/2017       725
42      4/11/2017       832

我想添加一个带有增量数字的新列，它将显示计数的每日增加/减少。如下表所示:

ID      Datetime_Scan   Count   Delta
2       4/6/2017        302     0
10      4/7/2017        391     89
18      4/8/2017        500     109
[...]

我应该在 MySQL 中做一种循环并计算每个日期与前一天的增量吗？谢谢。

Answer 1

您可以使用变量来获取它。

drop table if exists Statistics;
create table if not exists Statistics (ID int, Datetime_Scan date, Count int);
insert into Statistics values
( 2, '2017/4/6',        302),
(10, '2017/4/7',        391),
(18, '2017/4/8',        500),
(26, '2017/4/9',        605),
(34, '2017/4/10',       725),
(42, '2017/4/11',       832);

select Id, Datetime_Scan, Count, @last_count, 
       if (@last_count = 0, 0, Count - @last_count) as Delta,
       @last_count := Count
from
     (select @last_count := 0) x,
     (select id, Datetime_Scan, Count 
      from Statistics
      order by id, datetime_scan) y
order by id, datetime_scan;

drop table if exists Statistics;

| Id | Datetime_Scan       | Count | @last_count | Delta | @last_count := Count |
|----|---------------------|-------|-------------|-------|----------------------|
| 2  | 06.04.2017 00:00:00 | 302   | 0           | 0     | 302                  |
| 10 | 07.04.2017 00:00:00 | 391   | 302         | 89    | 391                  |
| 18 | 08.04.2017 00:00:00 | 500   | 391         | 109   | 500                  |
| 26 | 09.04.2017 00:00:00 | 605   | 500         | 105   | 605                  |
| 34 | 10.04.2017 00:00:00 | 725   | 605         | 120   | 725                  |
| 42 | 11.04.2017 00:00:00 | 832   | 725         | 107   | 832                  |

Rextester在这里:http://rextester.com/KOHDW53168