我有一个具有指定id、值、validate和值的表
MyTable如下所示：
+------+------------+---------------------+-------+
| ID | VALUEID | VALUEDATE | VALUE |
+------+------------+---------------------+-------+
| 1 | 2 | 2015-12-21 16:30:00 | 50 |
| 2 | 1 | 2015-12-22 16:33:00 | 54 |
| 3 | 8 | 2015-12-23 16:54:00 | 58 |
| 4 | 6 | 2015-12-24 17:11:00 | 62 |
| 5 | 8 | 2015-12-25 17:11:00 | 66 |
| 6 | 10 | 2015-12-26 10:01:00 | 70 |
| 7 | 7 | 2015-12-27 11:00:00 | 74 |
| 8 | 9 | 2015-12-28 12:03:00 | 78 |
| 9 | 7 | 2015-12-29 12:05:00 | 82 |
| 10 | 6 | 2015-12-30 12:05:00 | 86 |
| 11 | 3 | 2016-01-01 13:19:00 | 90 |
| 12 | 2 | 2016-01-02 13:20:00 | 94 |
| 13 | 7 | 2016-01-03 13:21:00 | 98 |
| 14 | 6 | 2016-01-04 13:51:00 | 102 |
| 15 | 9 | 2016-01-05 13:53:00 | 106 |
| 16 | 3 | 2016-01-06 14:51:00 | 110 |
| 17 | 6 | 2016-01-07 15:31:00 | 114 |
| 18 | 10 | 2016-01-08 15:32:00 | 118 |
| 19 | 7 | 2016-01-09 15:33:00 | 122 |
| 20 | 6 | 2016-01-10 15:34:00 | 126 |
+------+------------+---------------------+-------+
当我想要结果时，值id=6。我在用这个密码。
从value id=6的MyTable中选择id、valueid、valuedate、value
结果是这样的。
+------+------------+---------------------+-------+
| ID | VALUEID | VALUEDATE | VALUE |
+------+------------+---------------------+-------+
| 4 | 6 | 2015-12-24 17:11:00 | 62 |
| 10 | 6 | 2015-12-30 12:05:00 | 86 |
| 14 | 6 | 2016-01-04 13:51:00 | 102 |
| 17 | 6 | 2016-01-07 15:31:00 | 114 |
| 20 | 6 | 2016-01-10 15:34:00 | 126 |
+------+------------+---------------------+-------+
但我想计算一下结果。
我想数一数前一个条目是多少天。此外，还应进行小规模计算。B值（减去）A值（除以）天。
我试过这样的方法，但没有得到结果。
选择A.id，A.valueid，A.valuedate，A.value，（B.valuedate-
A.valuedate）作为valuedatediff，（B.value-A.value/valuedatediff）作为
valuecalc来自于B.valueid上的MyTable A内部联接MyTable B=
（A.valueid+1）其中A.valueid=6
不管怎样，我想得到这个结果。
+------+------------+---------------------+-------+---------------+-----------+
| ID | VALUEID | VALUEDATE | VALUE | VALUEDATEDIFF | VALUECALC |
+------+------------+---------------------+-------+---------------+-----------+
| 4 | 6 | 2015-12-24 17:11:00 | 62 | 5 | 4.8 |
| 10 | 6 | 2015-12-30 12:05:00 | 86 | 5 | 3.2 |
| 14 | 6 | 2016-01-04 13:51:00 | 102 | 3 | 4 |
| 17 | 6 | 2016-01-07 15:31:00 | 114 | 3 | 4 |
| 20 | 6 | 2016-01-10 15:34:00 | 126 | 0 | 0 |
+------+------------+---------------------+-------+---------------+-----------+
谢谢！
最佳答案
E.g.：
SELECT a.id
, a.valueid
, a.valuedate
, a.value
, b.value - a.value valuediff
, COALESCE(DATEDIFF(b.valuedate,a.valuedate),0) valuedatediff
, COALESCE((b.value - a.value)/(DATEDIFF(b.valuedate,a.valuedate)),0) valuecalc
FROM
( SELECT x.*
, MIN(y.id) min_id
FROM my_table x
LEFT
JOIN my_table y
ON y.valueid = x.valueid
AND y.id > x.id
WHERE x.valueid = 6
GROUP
BY x.id
) a
LEFT
JOIN my_table b
ON b.id = a.min_id;