MYSQL如何在MYSQL中执行两个日期之间的自定义月份差异? [英] MYSQL How to perform custom month difference between two dates in MYSQL?

问题描述

我的要求是分别计算两个月之间的总月数，然后分别计算破月(即，表中的第一个日期和第二个日期为当前日期).如果破损月份的总数大于15，则将其视为一个月的经验，如果小于15则不将其视为1个月的经验.

My requirement is to compute the total months and then broken months separately between 2 dates (ie first date from table and second date is current date). If broken months total count is > 15 then account it as one month experience and if its les than 15 don't account that as 1 month experience.

假设我在桌子上的日期为25/11/2018，当前日期为06/01/2019;整个月是12月，所以有1个月的经验；和残破的月份是11月和1月，所以现在我必须计算11月的6天和1月的6天的日期，所以12天且< =(lte)15，所以总经验将舍入为1个月的经验

Assume I have a date on table as 25/11/2018 and current date is 06/01/2019; the full month in between is December, so 1 month experience; and broken months are November and January, so now I have to count the dates which is 6 days in Nov and 6 days in Jan, so 12 days and is <= (lte) 15 so total experience will be rounded to 1 month experience

我提到了多个与从stackoverflow计算MYSQL中的日期差有关的问题，但是找不到任何可能的选项.MYSQL TIMESTAMPDIFF，TIMEDIFF，PERIOD_DIFF，DATE_DIFF中的内置函数未提供我所需的结果，因为它们的算法与我的计算要求不同.

I referred multiple questions related to calculating date difference in MYSQL from stackoverflow, but couldn't find any possible options. The inbuilt functions in MYSQL TIMESTAMPDIFF, TIMEDIFF, PERIOD_DIFF, DATE_DIFF are not giving my required result as their alogrithms are different from my calculation requirement.

关于如何在MYSQL中执行此计算并将其结果作为SQL语句的一部分获得的任何线索都将对我有所帮助.到达此值后，在同一SQL中，将验证该值是否在给定的值范围内.

Any clue on how to perform this calculation in MYSQL and arrive its result as part of the SQL statement will be helpful to me. Once this value is arrived, in the same SQL, that value will be validated to be within a given value range.

包括示例表结构和值:

table_name = "user"

id | name | join_date 
---------------------
1| Sam | 25-11-2017
2| Moe | 03-04-2017
3| Tim | 04-07-2018
4| Sal | 30-01-2017
5| Joe | 13-08-2018

我想从上表中找出根据上述逻辑以几个月为单位计算出其经验的用户.如果这些月份介于以下两个范围之内，则将获取这些用户以进行进一步处理.

I wanted to find out the users from above table whose experience is calculated in months based on the aforementioned logic. If those months are between either of following ranges, then those users are fetched for further processing.

table_name: "allowed_exp_range"
starting_exp_months | end_exp_months
-------------------------------------
0 | 6
9 | 24

例如:根据我的计算，Sam截至(10-12-2018)的经验是12 + 1个月= 13个月.由于13在9和9之间.24，Sam的记录是预期的输出之一.

For ex: Sam's experience till date (10-12-2018) based on my calculation is 12+1 month = 13 months. Since 13 is between 9 & 24, Sam's record is one of the expected output.

推荐答案

我认为此查询将满足您的要求.它使用

I think this query will do what you want. It uses

  (YEAR(CURDATE())*12+MONTH(CURDATE()))
- (YEAR(STR_TO_DATE(join_date, '%d-%m-%Y'))*12+MONTH(STR_TO_DATE(join_date, '%d-%m-%Y'))) -
- 1

获取用户整个月的体验次数，

to get the number of whole months of experience for the user,

  DAY(LAST_DAY(STR_TO_DATE(join_date, '%d-%m-%Y')))
- DAY(STR_TO_DATE(join_date, '%d-%m-%Y'))
+ 1

获取第一个月的天数，

DAY(CURDATE())

获取当月的天数.将两天的计数相加，如果总数> 15，则将整月数加1，例如

to get the number of days in the current month. The two day counts are summed and if the total is > 15, 1 is added to the number of whole months e.g.

SELECT id
     , name
     , (YEAR(CURDATE())*12+MONTH(CURDATE())) - (YEAR(STR_TO_DATE(join_date, '%d-%m-%Y'))*12+MONTH(STR_TO_DATE(join_date, '%d-%m-%Y'))) - 1 -- whole months
       + CASE WHEN DAY(LAST_DAY(STR_TO_DATE(join_date, '%d-%m-%Y'))) - DAY(STR_TO_DATE(join_date, '%d-%m-%Y')) + 1 + DAY(CURDATE()) > 15 THEN 1 ELSE 0 END -- broken month
       AS months
FROM user

我们可以将此表达式用作 user 和 allowed_exp_range 之间的 JOIN 条件，以查找在给定范围内具有经验的所有用户:

We can use this expression as a JOIN condition between user and allowed_exp_range to find all users who have experience within a given range:

SELECT u.id
     , u.name
     , a.starting_exp_months
     , a.end_exp_months
FROM user u
JOIN allowed_exp_range a
ON (YEAR(CURDATE())*12+MONTH(CURDATE())) - (YEAR(STR_TO_DATE(u.join_date, '%d-%m-%Y'))*12+MONTH(STR_TO_DATE(u.join_date, '%d-%m-%Y'))) - 1
       + CASE WHEN DAY(LAST_DAY(STR_TO_DATE(u.join_date, '%d-%m-%Y'))) - DAY(STR_TO_DATE(u.join_date, '%d-%m-%Y')) + 1 + DAY(CURDATE()) > 15 THEN 1 ELSE 0 END
       BETWEEN a.starting_exp_months AND a.end_exp_months

输出(对于您的示例数据，包括所有用户，因为他们都属于一种体验范围):

Output (for your sample data, includes all users as they all fit into one of the experience ranges):

id  name    starting_exp_months     end_exp_months
1   Sam     9                       24
2   Moe     9                       24
3   Tim     0                       6
4   Sal     9                       24
5   Joe     0                       6

我已经在dbfiddle上创建了一个小的 demo 步骤以达到结果.

I've created a small demo on dbfiddle which demonstrates the steps in arriving at the result.

这篇关于MYSQL如何在MYSQL中执行两个日期之间的自定义月份差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！