试图获得月数 [英] trying to get the number of months

问题描述

membership表

• membership_startdate(2011-01-12)
• membership_dueday值仅类似于日期(09,08,07)
• member_id

member表

• member_id

如何计算会员到现在为止已支付的月数并考虑在内(membership_dueday)?假设membership_startdate是2011-01-01且membership_dueday是15，那么到现在为止的月份数是5.5

How can I get count of the number of months that the member has paid till now and taking into consideration (membership_dueday)? Suppose if membership_startdate is 2011-01-01 and membership_dueday is 15, the the number of months count up to till now is 5.5

我已经尝试过此代码

SELECT COUNT(NUMBEROFMONTHS) 
  FROM membership 
 WHERE NUMBEROFMONTHS = PERIOD_DIFF(membership.membership_startdate, CURDATE());

它给出了这样的错误:

错误代码:1054
字段列表"中的未知列"NUMBEROFMONTHS"

Error Code: 1054
Unknown column 'NUMBEROFMONTHS' in 'field list'

...但未考虑membership_dueday ...

...but it does not taking into account the membership_dueday...

推荐答案

1054错误是因为该列在FROM子句中定义的表中不存在.此外，WHERE子句不用于设置变量或列别名-用于过滤返回的行.

The 1054 error is because the column does not exist in the table(s) defined in the FROM clause. Additionally, the WHERE clause is not used to set a variable, or column alias -- it's for filtering rows returned.

使用 DATEDIFF :

SELECT t.member_id,
       DATEDIFF(LEAST(NOW(), t.membership_dueday), t.membership_startdate) / 30
  FROM MEMBERSHIP t

LEAST 函数将返回两个日期中的最低者，因此如果到期日期在将来，它将使用当前日期.如果您希望将GREATEST功能撤消，可以使用它.

The LEAST function will return the lowest of the two dates, so it will use the current date if the due date is in the future. You can use the GREATEST function if you want that reversed.

这篇关于试图获得月数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！