mysql - SQL 查询如何使用临时字段值获取下一行日期？

Question

这是我的原始查询....如果你能看到next_row_date的日期'2013-01-01'不是fld_date的最后一条记录并且next_row_date的最后一条记录是'2013-01-15'必须是0.

+-----+---+------------+---------------+| seq | i | fld_Date   | next_row_date |+-----+---+------------+---------------+|  41 | 1 | 2012-10-08 | 2012-10-15    ||  42 | 2 | 2012-10-15 | 2012-10-22    ||  43 | 3 | 2012-10-22 | 2012-10-29    ||  44 | 4 | 2012-10-29 | 2012-11-05    ||  45 | 5 | 2012-11-05 | 2012-11-12    ||  46 | 6 | 2012-11-12 | 2012-11-19    ||  47 | 7 | 2012-11-19 | 2013-01-01    ||  49 | 8 | 2013-01-08 | 2013-01-15    |+-----+---+------------+---------------+

Here's the output I wanted. Can you help me guyz with this query?

+-----+---+------------+---------------+| seq | i | fld_Date   | next_row_date |+-----+---+------------+---------------+|  41 | 1 | 2012-10-08 | 2012-10-15    ||  42 | 2 | 2012-10-15 | 2012-10-22    ||  43 | 3 | 2012-10-22 | 2012-10-29    ||  44 | 4 | 2012-10-29 | 2012-11-05    ||  45 | 5 | 2012-11-05 | 2012-11-12    ||  46 | 6 | 2012-11-12 | 2012-11-19    ||  47 | 7 | 2012-11-19 | 2013-01-08    ||  49 | 8 | 2013-01-08 | 0             |+-----+---+------------+---------------+

SELECT 
        db_lms.a.seq,
        (@i:=@i+1)AS i,
        db_lms.a.fld_Date,
        (db_lms.b.fld_Date)AS next_row_date
 FROM db_lms.lms_savings a, db_lms.lms_savings b, (SELECT @i:=0) ii
WHERE (db_lms.a.seq = db_lms.b.seq-1) ORDER BY db_lms.a.seq ASC;

Answer 1

尝试

SELECT seq, @i := @i + 1 i, fld_Date, next_date
FROM
(
  SELECT seq, @d next_date, @d := fld_Date fld_Date
    FROM lms_savings, (SELECT @d := 0) d
  ORDER BY seq DESC
) q, (SELECT @i := 0) n 
ORDER BY seq

输出:

| SEQ | I |   FLD_DATE |  NEXT_DATE |-------------------------------------|  41 | 1 | 2012-10-08 | 2012-10-15 ||  42 | 2 | 2012-10-15 | 2012-10-22 ||  43 | 3 | 2012-10-22 | 2012-10-29 ||  44 | 4 | 2012-10-29 | 2012-11-05 ||  45 | 5 | 2012-11-05 | 2012-11-12 ||  46 | 6 | 2012-11-12 | 2012-11-19 ||  47 | 7 | 2012-11-19 | 2013-01-08 ||  49 | 8 | 2013-01-08 |          0 |

这是 SQLFiddle 演示。

注意:即使您的示例数据 47-49 中的序列号存在间隙，查询仍将有效