mysql - 从 MySQL 中的上一行中选择多列

Question

假设我有一个这样的表:

| id |       date | name | value |
|----|------------|------|-------|
|  0 | 2017-01-14 |  foo |   one |
|  1 | 2017-01-17 |  bar |   two |
|  2 | 2017-01-18 | john |  five |
|  3 | 2017-01-19 |  doe |   ten |

(其中 date 不一定要排序)

我希望能够选择前 行的一些值(基于日期)。这样的功能可以通过以下查询来实现:

SELECT 
  *, 
  (SELECT 
     name 
   FROM 
     example e2 
   WHERE 
     e2.dt < e1.dt 
   ORDER BY dt DESC 
   LIMIT 1
   ) as prev_name 
FROM example e1

结果表:

| id |         dt | name | value | prev_name |
|----|------------|------|-------|-----------|
|  0 | 2017-01-14 |  foo |   one |    (null) |
|  1 | 2017-01-17 |  bar |   two |       foo |
|  2 | 2017-01-18 | john |  five |       bar |
|  3 | 2017-01-19 |  doe |   ten |      john |

现在，这工作得很好。但是，如果我可以轻松地从上一行中选择 多个 列，则结果会更好:

| id |         dt | name | value | prev_name | prev_value |    prev_dt |
|----|------------|------|-------|-----------|------------|------------|
|  0 | 2017-01-14 |  foo |   one |    (null) |     (null) |     (null) |
|  1 | 2017-01-17 |  bar |   two |       foo |        one | 2017-01-14 |
|  2 | 2017-01-18 | john |  five |       bar |        two | 2017-01-17 |
|  3 | 2017-01-19 |  doe |   ten |      john |       five | 2017-01-18 |

这当然可以通过简单地将子查询 (SELECT [..] FROM example e2 ...) 多次复制到查询中来完成，但我想这不是更好的方法去。我发现了几个关于 SO 的问题，要么“如何从前一行中选择记录”或“如何使用子查询选择多列”问题，但不能同时解决这两个问题.后一个问题主要通过使用 JOIN 语句来解决，但我认为这不能与“上一行”的情况结合使用。所以我的问题是:什么是生成最后结果的更好方法，而不是为我们需要的每一列复制子查询？

编辑。作为一个额外的约束，我没有在原始问题中包含，“previous”实际上可能与前一行不同，而是“满足条件的前一行” ”。所以假设我的表包含一个额外的 boolean 列 b

| id |         dt | name | value | b |
|----|------------|------|-------|---|
|  0 | 2017-01-14 |  foo |   one | 1 |
|  1 | 2017-01-17 |  bar |   two | 0 |
|  2 | 2017-01-18 | john |  five | 1 |
|  3 | 2017-01-19 |  doe |   ten | 0 |

我希望“前一行”是 b = 1 的前一行，因此所需的结果将是:

| id |         dt | name | value | b | prev_name | prev_value |    prev_dt |
|----|------------|------|-------|---|-----------|------------|------------|
|  0 | 2017-01-14 |  foo |   one | 1 |    (null) |     (null) |     (null) |
|  1 | 2017-01-17 |  bar |   two | 0 |       foo |        one | 2017-01-14 |
|  2 | 2017-01-18 | john |  five | 1 |       foo |        one | 2017-01-14 |
|  3 | 2017-01-19 |  doe |   ten | 0 |      john |       five | 2017-01-18 |

我认为这仍然可以通过 James Scott 的回答来完成，只需在 b = 1 时更新变量，使用 IF 语句，但也许有在这种情况下可能的另一种解决方案。

编辑 SQLfiddle

Answer 1

像这样的东西将返回“上一个”行的 ID。

 SELECT x.*
      , MAX(y.id) prev_id
   FROM example x
   LEFT
   JOIN example y
     ON y.id < x.id 
    AND y.b = 1
  GROUP
     BY x.id;

我将返回与该行相关的其余数据作为读者的练习。