mysql& php-如何从多个日期范围中计算总的经验年数？ [英] mysql & php - How to calculate total year of experience from multiple date range?

问题描述

我想计算每个特定用户ID的总体验年和月。下表中的user_id可以是多个，如下所示。在这里，对于user_id 14，总经验范围是2010年1月3日至2013年11月30日，预期结果将是3年11个月。如何在php或mysql中做到这一点？

I want to calculate total experience year and month of each particular user id. user_id can be multiple in the table like the following. Here for user_id 14, the total experience range will be from 2010-01-03 to 2013-11-30 and the expected result will be 3 year 11 month. How can I do it in either php or mysql?

推荐答案

注释与您的情况相关，但不完整。

Comments are relevant but incomplete for your case.

如果 exp_to 不为空：

select user_id, sum( datediff(exp_to, exp_from) +1 )
from experience
group by user_id;

注意：

• 不要忘记总和 +1 ，因为对于用户25，它将返回0，而我们可以体面地认为用户工作了1天
$用户14的b $ b
• 根据您的屏幕截图，总计880天：您必须计算月份数（例如，使用PHP）。

• don't forget +1 in the sum, because for user 25 it would return 0, whereas we can decently consider the user worked 1 day
• for user 14, based on your screenshot, the total is 880 days: you'll have to calculate the number of months (with PHP, for example).

如果 exp_to 可为空（如果用户仍在相同位置工作，这是有道理的）：

If exp_to is nullable (which makes sense if user is still working at the same position):

select user_id, sum( datediff( ifnull(exp_to, now()), exp_from) +1 )
from experience
group by user_id;

这篇关于mysql& php-如何从多个日期范围中计算总的经验年数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！