mysql - 使用附加条件连接表

Question

我有点没主意了。我有一个表，其中包含以下数据:

ID  | timedate     | value | ...
 1  | 04-16-2017   | 3     | ...
 1  | 04-20-2017   | 4     | ...
 1  | 04-24-2017   | 10    |...[approx 100k different IDs)

现在，用户搜索(例如)ID = 1。在我看来，我想显示不同的列和值的差异。但差异应该只是从最后一个时间日期到倒数第二个时间日期，如下所示:

ID  |  timedate   | value   | dif.Value
1   | 04-24-2017  |   10    |  6  [10-4]

我实际的 mySQL-Query 看起来像这样:

SELECT 
 tab.Id AS `tab__Id`, 
 tab.timedate AS `tab__timedate`, 
 tab.value AS `tab__value`, 
 tab.value - t2.value AS `difValue`, 
FROM 
 tab tab
INNER JOIN tab3 t3 ON t3.Id = tab.Id 
INNER JOIN tab t2 ON t2.Id = tab.Id 
  WHERE 
  (t3.Id like '1' 
   AND tab.timedate IN (
   SELECT 
    max(tab.timedate) 
   FROM 
    tab
  GROUP BY 
    tab.Id
   )
  ) 
GROUP BY 
 tab.Id
LIMIT 
10 OFFSET 0

来自cakePHP3 =

$search= $tab->find('all')
             ->select(['Id', 'tab.timedate ', 'tab.value','difValue' => 
               'tab.value - t2.value'])
             ->andWhere(['t3.Id LIKE' => $id])
             ->where(['tab.timedate IN (SELECT max(tab.timedate) FROM tab 
               GROUP BY tab.Id)'])
             ->distinct(['tab.Id'])
                ->join([
                    't3' => [
                        'table' => 'tab3',
                        'conditions' => 't3.Id= tab.Id ',
                    ],
                    't2' => [
                        'table' => 'tab',
                        'conditions' => ' t2.Id= tab.Id
                    ]
                ]);

如何设置Join(t2)，他为t2.value取倒数第二个日期？我尝试将其添加到我的条件 t2 中，但它需要第一个时间日期

 ...AND tab.timedate > t2.timedate - INTERVAL 1 DAY'

也许还有另一种方法可以解决这个问题。我只是想计算差异。最后一个时间日期:值和倒数第二个时间日期:值(最后一个值 - 倒数第二个值=>“difValue”)

Answer 1

为了清楚起见，我添加了而不是减去了 - 并且只是为了有所不同。请注意，外部查询中对“当前”的引用实际上只是对主键的引用。

SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

SELECT total 
  FROM 
     ( SELECT @i:=@i+i total
            , @i:=i current 
         FROM ints
            , (SELECT @i:=0) vars 
        ORDER 
           BY i DESC 
        LIMIT 2
     ) x 
 ORDER 
    BY current 
 LIMIT 1;
+-------+
| total |
+-------+
|    17 |
+-------+