使用 Mysql group by ID 在同一列中减去两行的值 [英] Subtract two rows' values within the same column using Mysql group by ID

问题描述

我有一张如下所示的表格.我想做一个分区，然后减去同一列中的值以获得差异.

I have a table as shown below. I want to do a partition and then subtract the values in the same column to get the difference.

由于 MySQL 中没有可用的分区或等效功能，谁能给我一个如何去做的想法?我已经计算出分区，但没有计算出其他部分.

Since there is no partition or equivalent function available in MySQL, can anyone give me an idea of how to do it? I have worked out the partition but not the other part.

SELECT ID,Date,
       @row_number:=CASE WHEN @ID=ID THEN @row_number+1 ELSE 1 END AS row_number,
       @ID:=ID AS ID,value
FROM table1, (SELECT @row_number:=0,@ID:='') AS t 
ORDER BY id,Date;

输入:

 ID           Date        Value
1001    24-07-2017 09:43    10
1002    24-07-2017 09:43    11
1003    22-08-2017 21:42    10
1001    07-09-2017 20:33    11
1003    07-09-2017 20:33    44
1002    24-07-2017 09:43    55
1004    07-09-2017 20:33    66

输出应该是:

rowno  ID         Date        Value Diff
1     1001  24-07-2017 09:43    10  N/A
2     1001  07-09-2017 20:33    11  1
1     1002  24-07-2017 09:43    11  N/A
2     1002  24-07-2017 09:43    55  44
1     1003  22-08-2017 21:42    10  n/A
2     1003  07-09-2017 20:33    44  34
1     1004  07-09-2017 20:33    66  N/A

推荐答案

您的日期不正确.当您按此列订购时，您首先按天订购.正确的顺序是年-月-日.使用正确的数据类型、日期时间或时间戳.为此，您可以执行以下操作:

Your dates are incorrect. When you order by this column, you order by day first. Proper ordering is year-month-day. Use the proper datatype, datetime or timestamp. To do this you can do the following:

alter table table1 add column d datetime;
update table1 set d = str_to_date(`Date`, '%d-%m-%Y %H:%i');
alter table table1 drop column `Date`;

获得所需结果的查询是:

The query to get your desired result is then:

SELECT ID, d, Value,
       IF(@ID=ID, Value - @prevV, 'N/A') AS diff,
       @row_number:=CASE WHEN @ID=ID THEN @row_number+1 ELSE 1 END AS row_number,

       @prevV := Value,
       @ID:=ID AS ID
FROM table1
, (SELECT @row_number:=0, @ID:='', @prevV := NULL) AS t 
ORDER BY id, d;

您所要做的就是添加另一个变量来保存前一行的值.

All you have to do is add another variable to hold the value of the previous row.

• 在 sqlfiddle
中查看它的实时运行情况
• 这里是 另一个 sqlfiddle 来展示当每个 3 行时会发生什么身份证

• see it working live in an sqlfiddle
• here's another sqlfiddle to showcase what happens when you have 3 rows per ID

这篇关于使用 Mysql group by ID 在同一列中减去两行的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！