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 |
+--------+---------+------+----------+--------+

问题：如何在最终结果中添加一列，即 2 行值的差值？ 例如，我试图获取列"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，然后尝试左加入 Q1 和 Q2...(在 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

Answer 2

如果您的查询已经生成以下内容：

+--------+---------+------+----------+--------+
| 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 |
+--------+---------+------+----------+--------+

然后只需添加一个子查询。

SELECT   T1.*, COALESCE(T1.iv_begin - T2.iv_begin, 0) as iv_diff 
FROM ( YourQuery ) as T1
LEFT JOIN ( YourQuery ) as T2
ON T1.rownum = T2.rownum - 1

但请注意，T1 和 T2 需要使用不同的@cnt变量来创建rownum