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 . 然后使用子查询以适当的方式呈现数据。

我建议像这样使用self-join

select d1.post, 
       d1.d `start DATE`, 
       min(d2.d) `end DATE`, 
       timestampdiff(month, d1.d, min(d2.d)) `MONTHS`
from data d1
left join data d2 on d1.d < d2.d
group by d1.post, d1.d

dbfiddle演示
数据表是sql的结果。可以使用 WITH 或者也可以使用子查询。

试试这个。。。。

SELECT T1.POST,T1.DATE  ,T2.DATE,DATEDIFF(MONTH,T1.DATE,T2.DATE)
 FROM(

   SELECT ROW_NUMBER()OVER(ORDER BY DATE DESC) AS SlNo,*
   FROM Mytable)T1
   LEFT JOIN (SELECT ROW_NUMBER()OVER(ORDER BY DATE DESC)+1 AS SlNo,*
   FROM Mytable)T2
   ON(T1.SlNo = T2.SlNo  )

SELECT * ,Datediff(Month,[Date],endate) 
FROM
(
SELECT *,Lead( [Date], 1, Null) OVER (
     ORDER BY [Date]) AS Endate --INTO SourceTable 
FROM
(                  
SELECT 'Senior Software Engg.' POST    ,   '2018-04-18' DATE UNION ALL
SELECT 'Software Engg.'        POST    ,   '2017-04-18' DATE UNION ALL
SELECT 'Assoc. Software Engg.'  POST   ,    '2016-04-18' DATE 
)A
)B
ORDER BY [Date] desc

您可以使用变量获取上一个日期：

SELECT id, post, date,
       (CASE WHEN (@tmp_prevd := @prevd) = NULL THEN NULL  -- never happens
             WHEN (@prevd := date) = NULL THEN NULL  -- never happens
             ELSE @tmp_prevd
        END) as prev_date
FROM (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
     ) ud CROSS JOIN
     (SELECT @prevd := NULL) params;

这很棘手，因为对一个变量的所有引用都需要在同一个表达式中。这就是为什么它使用 CASE 以一种相当神秘的方式。
在mysql 8.0和基本上所有其他数据库中，您可以使用 LEAD() 相反。