mysql - 如何从下一条记录中获取列值

Question

我有以下结果集:

POST                   |    DATE
--------------------------------------
Senior Software Engg.  |    2018-04-18
Software Engg.         |    2017-04-18
Assoc. Software Engg.  |    2016-04-18

SQL查询:

SELECT DISTINCT designation_id as id, d.title as POST, DATE(dt_datetime) as DATE
    FROM users_history_check u
    INNER JOIN
    designations d 
    ON d.id = u.designation_id
    WHERE u.id = $userID
    ORDER BY DATE DESC

我想获取下一条记录并以月为单位进行日期差异计算，并显示记录。

预期输出:

POST                   |    Start DATE  |  End DATE   |  MONTHS
---------------------------------------------------------------
Senior Software Engg.  |    2018-04-18  |   -         |
Software Engg.         |    2017-04-18  |  2018-04-18 |  12
Assoc. Software Engg.  |    2016-04-18  |  2017-04-18 |  12

类似的东西:

SELECT DISTINCT designation_id as id, d.title as POST, DATE(dt_datetime) as Start DATE, NEXT_RECORD(DATE(dt_datetime)) as End DATE, DATEDIFF(Start DATE, End DATE) as MONTHS....

非常感谢任何帮助。谢谢。

Answer 1

SELECT `POST`, 
        `DATE`,
        IFNULL(END_DATE,'') AS END_DATE,
        IFNULL(MONTH,'') AS MONTH
FROM
(SELECT `POST`, 
        `DATE`,
        @prev AS END_DATE,
        TIMESTAMPDIFF(month,DATE,@prev) AS MONTH,
        @prev := T.DATE AS VarDate
FROM Table1 T,
(SELECT @prev:=null)R
) T1

输出

POST                    DATE        END_DATE    MONTH
Senior Software Engg.   2018-04-18      
Software Engg.          2017-04-18  2018-04-18  12
Assoc. Software Engg.   2016-04-18  2017-04-18  12

演示链接

http://sqlfiddle.com/#!9/33260/15

解释:

在子查询中，我将 Date 值保存在 @prev 变量中，并在每一行中使用该变量计算 END_DATE 之前从 Date 列分配当前日期值。然后使用子查询以适当的方式呈现数据。