MySQL - 从前一行减去值，分组依据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13196190/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - Subtracting value from previous row, group by
提问by user1794142
I need to have the consumption value base on previous one by SN number. This is my data:
我需要根据 SN 号获得基于前一个的消耗值。这是我的数据：
TABLE EnergyLog
表格能量记录
SN Date Value
2380 2012-10-30 00:15:51 21.01
2380 2012-10-31 00:31:03 22.04
2380 2012-11-01 00:16:02 22.65
2380 2012-11-02 00:15:32 23.11
20100 2012-10-30 00:15:38 35.21
20100 2012-10-31 00:15:48 37.07
20100 2012-11-01 00:15:49 38.17
20100 2012-11-02 00:15:19 38.97
20103 2012-10-30 10:27:34 57.98
20103 2012-10-31 12:24:42 60.83
This is the result I need:
这是我需要的结果：
SN Date Value consumption
2380 2012-10-30 00:15:51 21.01 0
2380 2012-10-31 00:31:03 22.04 1.03
2380 2012-11-01 00:16:02 22.65 0.61
2380 2012-11-02 00:15:32 23.11 0.46
20100 2012-10-30 00:15:38 35.21 0
20100 2012-10-31 00:15:48 37.07 1.86
20100 2012-11-01 00:15:49 38.17 1.1
20100 2012-11-02 00:15:19 38.97 0.8
20103 2012-10-30 10:27:34 57.98 0
20103 2012-10-31 12:24:42 60.83 2.85
回答by DRapp
Working with MySQL variables is great, its like inline program variable assignments. First, the FROM clause "declares" the @ variables for you, defaulting to blank. Then query the records in the expected order you want them. It makes a single pass through the data instead of via repeated subqueries which can be time intensive.
使用 MySQL 变量很棒，就像内联程序变量分配一样。首先，FROM 子句为您“声明”@ 变量，默认为空白。然后按照您想要的预期顺序查询记录。它通过数据进行单次传递，而不是通过重复的子查询，这可能是时间密集型的。
For each row read, compare the @lastSN with the SN of the current record. If different, always return 0. If it IS the same, compute the simple difference. Only AFTER that compare is done, set the @lastSN and @lastValue equal to that of the current record for the next records comparison.
对于读取的每一行，将@lastSN 与当前记录的SN 进行比较。如果不同，则始终返回 0。如果相同，则计算简单差异。只有在比较完成后，才将@lastSN 和@lastValue 设置为等于当前记录的值，以便进行下一条记录比较。
select
EL.SN,
EL.Date,
EL.Value, --remove duplicate alias
if( @lastSN = EL.SN, EL.Value - @lastValue, 0000.00 ) as Consumption,
@lastSN := EL.SN,
@lastValue := EL.Value
from
EnergyLog EL,
( select @lastSN := 0,
@lastValue := 0 ) SQLVars
order by
EL.SN,
EL.Date
回答by bidifx
This should do the trick:
这应该可以解决问题：
SELECT l.sn,
l.date,
l.value,
l.value - (SELECT value
FROM energylog x
WHERE x.date < l.date
AND x.sn = l.sn
ORDER BY date DESC
LIMIT 1) consumption
FROM energylog l;
See SQLFiddle: http://sqlfiddle.com/#!2/b9eb1/8
见 SQLFiddle：http://sqlfiddle.com/#!2/b9eb1/8
回答by MatBailie
A near universal solution is to join the data on to itself, to find the previous record, by including a correlated sub-query in the join condition...
一个近乎通用的解决方案是将数据连接到自身，通过在连接条件中包含相关子查询来查找先前的记录......
SELECT
ThisLog.*,
COALESCE(ThisLog.Value - PrevLog.Value, 0) AS consumption
FROM
EnergyLog AS ThisLog
LEFT JOIN
EnergyLog AS PrevLog
ON PrevLog.SN = ThisLog.SN
AND PrevLog.Date = (SELECT MAX(Date)
FROM EnergyLog
WHERE SN = ThisLog.SN
AND Date < ThisLog.Date)
This performs best with one index covering both (SN, Date).
这在一个索引覆盖两者的情况下表现最佳(SN, Date)。
回答by limion
You can join two rows of the same table like this:
您可以像这样连接同一个表的两行：
SELECT this.*, prev.*
FROM tbl this
INNER JOIN tbl prev ON prev.id =
(
SELECT max(t.id)
FROM tbl t
WHERE t.id < this.id
)
WHERE ...
So your case will look like:
所以你的情况看起来像：
SELECT this.SN, this.Date, this.Value, (this.Value - prev.Value) AS consumption
FROM EnergyLog this
INNER JOIN EnergyLog prev ON prev.Date =
(
SELECT max(t.Date)
FROM EnergyLog t
WHERE t.Date < this.Date
)
回答by salona
Can you please try the below query once.
您能否尝试一次以下查询。
SELECT e1.*,
(SELECT Value
FROM EnergyLog e2
WHERE e2.sn = e1.sn AND e2.date < e1.date
ORDER BY date DESC
LIMIT 1)-l.Value consumption
FROM EnergyLog e1;
