MySQL - 如何包含与嵌套选择结果相差 2 行的列？

Question

设置:我有两个类似于以下的表:

Table: tickets
+------+------+------------+----------+----------+--------+-----+--+
| Site | Rack | Start Date | End Date | iv_begin | iv_end | ... |  |
+------+------+------------+----------+----------+--------+-----+--+
| 1    | 1    | 2016       | 2017     | 900      | 1000   | ... |  |
| 1    | 1    | 2016       | 2017     | 800      | 900    | ... |  |
| 1    | 1    | 2016       | 2017     | 700      | 800    | ... |  |
| 1    | 1    | 2016       | 2017     | 600      | 650    | ... |  |
+------+------+------------+----------+----------+--------+-----+--+

Table: sites
+----+----------+
| ID | sitename |
+----+----------+
| 1  | Atlanta  |
| 2  | Boston   |
+----+----------+

首先，我必须使用嵌套选择来获取结果表，然后从中进行查询。例如:

SELECT Q1.rownum, Q1.name AS "Site", Q1.rack, Q1.iv_begin, Q1.iv_end
FROM    
  (
  SELECT (@cnt := @cnt + 1) AS rownum, 
  S1.name, T1.rack, T1.batch_start, T1.batch_end, 
  T1.batch, T1.iv_begin, T1.iv_end      
  FROM tickets T1       
  LEFT JOIN sites S1 ON T1.Site = S1.ID         
  CROSS JOIN (SELECT @cnt := 0) AS dummy1       
  WHERE T1.rack = 1         
  ORDER BY 
   T1.batch DESC, 
   T1.iv_begin DESC, 
   T1.iv_end DESC       
   LIMIT 200
  ) 
  AS Q1

要获得与此类似的结果:

+--------+---------+------+----------+--------+
| rownum |  Site   | Rack | iv_begin | iv_end |
+--------+---------+------+----------+--------+
|      1 | Atlanta |    1 |      900 |   1000 |
|      2 | Atlanta |    1 |      800 |    900 |
|      3 | Atlanta |    1 |      700 |    800 |
|      4 | Atlanta |    1 |      600 |    650 |
+--------+---------+------+----------+--------+

问题:如何在最终结果中添加两行值之差的列？例如，我试图获取列“iv_diff”= (rownum[N] iv_begin) - (rownum[N+1] iv_end)。 iv_end 值应与前一行的 iv_begin 值匹配。 iv_diff 列用于查明是否不是这种情况，如果是，差异是什么。

因此结果表应如下所示:

+--------+---------+------+----------+--------+---------+
| rownum |  Site   | Rack | iv_begin | iv_end | iv_diff |
+--------+---------+------+----------+--------+---------+
|      1 | Atlanta |    1 |      900 |   1000 |       0 |
|      2 | Atlanta |    1 |      800 |    900 |       0 |
|      3 | Atlanta |    1 |      700 |    800 |       0 |
|      4 | Atlanta |    1 |      600 |    650 |      50 |
+--------+---------+------+----------+--------+---------+

我尝试复制/粘贴相同的选择，以便我也有结果 Q2，然后尝试 LEFT JOIN Q1 和 Q2...(ON Q1.rownum = Q2.rownum+1)...但是我似乎无法让 iv_diff 列返回我需要的结果(有时它给出累积和，这是不正确的)。

预先感谢您的帮助!

Answer 1

您可以使用另一个用户变量来保存上一行的值。

SELECT Q1.rownum, Q1.name AS "Site", Q1.rack, Q1.iv_begin, Q1.iv_end, Q1.iv_diff
FROM    
  (
  SELECT (@cnt := @cnt + 1) AS rownum, 
      S1.name, T1.rack, T1.batch_start, T1.batch_end, 
      T1.batch, T1.iv_begin, T1.iv_end, 
        IF(@prev_begin IS NULL, 0, T1.iv_end - @prev_begin) AS iv_diff, @prev_begin := T1.iv_begin
  FROM tickets T1       
  LEFT JOIN sites S1 ON T1.Site = S1.ID         
  CROSS JOIN (SELECT @cnt := 0, @prev_begin := NULL) AS dummy1       
  WHERE T1.rack = 1         
  ORDER BY 
   T1.batch DESC, 
   T1.iv_begin DESC, 
   T1.iv_end DESC       
   LIMIT 200
  ) 
  AS Q1