mysql - 如何在MySQL中计算日期差异

Question

我有下表:

|    ID |          HIRE_DATE |     PROMOTION_DATE |
|-------|-----------------------------------------|
| 10008 |      June, 12 1989 |   October, 15 2012 |
| 10021 |      June, 21 1999 |   October, 01 2012 |
| 10021 |      June, 21 1999 |  December, 27 2010 |
| 10029 |      June, 29 2007 |      June, 10 2013 |
| 10029 |      June, 29 2007 |      July, 01 2009 |
| 10058 | September, 12 1983 |       May, 31 2010 |
| 10100 | September, 20 1999 |  November, 14 2011 |
| 10101 |     April, 24 2006 |     March, 05 2012 |
| 10101 |     April, 24 2006 |      July, 01 2006 |
| 10122 |      July, 25 2005 |     March, 05 2012 |
| 10122 |      July, 25 2005 | September, 01 2009 |
| 10163 |      July, 06 2004 |      June, 14 2010 |
| 10163 |      July, 06 2004 |      July, 01 2007 |
| 10251 |      June, 06 1994 |     April, 01 2008 |
| 10279 | September, 01 2000 |  December, 01 2007 |
| 10320 |      July, 19 2004 | September, 19 2011 |
| 10320 |      July, 19 2004 |  December, 01 2009 |
| 10320 |      July, 19 2004 |   October, 01 2007 |

我想计算某人获得晋升需要多长时间。因此，对于第一个人10008，晋升时间将为2012年10月15日至雇用日期1989年6月12日之间的时间。

第2人有2次促销，因此第1次促销的时间在2010年12月27日和1999年6月21日之间会有所不同； 2012年10月1日和2010年12月27日之间的第二次促销事件将有所不同。

我可以在 Excel 中执行此操作，但不知道如何在 MySQL 中执行此操作。 Excel 无法处理大数据集，因此在 Excel 中进行计算时变得很麻烦。

下面是我想要的结果:

|    ID |          HIRE_DATE |     PROMOTION_DATE | PROMOTION_TIME (year)
-------------------------------------------------------------------|
| 10008 |      June, 12 1989 |   October, 15 2012 |             23 |
| 10021 |      June, 21 1999 |   October, 01 2012 |              2 |
| 10021 |      June, 21 1999 |  December, 27 2010 |             12 |
| 10029 |      June, 29 2007 |      June, 10 2013 |              4 |
| 10029 |      June, 29 2007 |      July, 01 2009 |              2 |
| 10058 | September, 12 1983 |       May, 31 2010 |             27 |
| 10100 | September, 20 1999 |  November, 14 2011 |             12 |
| 10101 |     April, 24 2006 |     March, 05 2012 |              6 |
| 10101 |     April, 24 2006 |      July, 01 2006 |              0 |
| 10122 |      July, 25 2005 |     March, 05 2012 |              3 |
| 10122 |      July, 25 2005 | September, 01 2009 |              4 |
| 10163 |      July, 06 2004 |      June, 14 2010 |              3 |
| 10163 |      July, 06 2004 |      July, 01 2007 |              3 |
| 10251 |      June, 06 1994 |     April, 01 2008 |             14 |
| 10279 | September, 01 2000 |  December, 01 2007 |              7 |
| 10320 |      July, 19 2004 | September, 19 2011 |              2 |
| 10320 |      July, 19 2004 |  December, 01 2009 |              2 |
| 10320 |      July, 19 2004 |   October, 01 2007 |              3 |

有人可以帮忙吗？

谢谢

Answer 1

这有点痛苦，因为MySQL不支持lag()函数。您可以使用相关子查询来代替。所以:

select id, hire_date, promotion_date,
       datediff(promotion_date, coalesce(prev_promotion_date, hire_date)) as days
from (select t.*,
             (select max(promotion_date)
              from table t2
              where t2.id = t.id and
                    t2.promotion_date < t.promotion_date
             ) as prev_promotion_date
      from table t
     ) t

如果您想要以年为单位的值，我只需除以 365.25，并使其足够接近。如果您需要更高的准确性，您可以查看 this .