MySQL用TIMESTAMPDIFF计算两个日期的月份差问题

问题：计算A日期和B日期按月份对比返回相差几年，不满一年算一年

首先使用MySQL的 TIMESTAMPDIFF + MOUTH 解决，但MySQL不满一个月不进行计算，意为 2017-05-29 ~ 2021-06-01 需要返回5，TIMESTAMPDIFF返回是4

解决方案： 对比两日期的天数，如果数值不一样认为不满一个月，加一操作

数据库：

SQL语句：

SELECT 
(
	case
			WHEN ( `b`.`LASTINSPECTTYPE` = '改造' ) THEN
				 (
						CASE
							WHEN ( (timestampdiff( MONTH, date_format( `b`.`remouldinspectdate`, '%Y-%m-%d' ), curdate()) mod 12 ) = 0 ) THEN 
							(
								case 
									when (date_format(curdate(),'%e')!=date_format(b.remouldinspectdate,'%e') ) THEN ceiling( (timestampdiff(MONTH,date_format(b.remouldinspectdate,'%Y-%m-%d'),(curdate() + INTERVAL 1 YEAR )) / 12 ) ) 	
									ELSE ceiling( (timestampdiff(MONTH,date_format(b.remouldinspectdate,'%Y-%m-%d'),curdate()) / 12 ) ) 
								END
							)							
						 ELSE ceiling( (timestampdiff(MONTH,date_format(b.remouldinspectdate,'%Y-%m-%d'),curdate()) / 12 ) ) 
						END 
					) 
			WHEN ( `b`.`LASTINSPECTTYPE` = '安装' ) THEN
				  (
						CASE
							WHEN ( (timestampdiff( MONTH, date_format( `b`.`INSTALLINSPECTDATE`, '%Y-%m-%d' ), curdate()) mod 12 ) = 0 ) THEN 
							(
								case 
									when (date_format(curdate(),'%e')!=date_format(b.INSTALLINSPECTDATE,'%e') ) THEN ceiling( (timestampdiff(MONTH,date_format(b.INSTALLINSPECTDATE,'%Y-%m-%d'),(curdate() + INTERVAL 1 YEAR )) / 12 ) ) 	
									ELSE ceiling( (timestampdiff(MONTH,date_format(b.INSTALLINSPECTDATE,'%Y-%m-%d'),curdate()) / 12 ) ) 
								END
							)							
						 ELSE ceiling( (timestampdiff(MONTH,date_format(b.INSTALLINSPECTDATE,'%Y-%m-%d'),curdate()) / 12 ) ) 
						END 
					) 
			ELSE '' 
			END 
	) AS `USEYEARS`
	
 from helloelv b

返回结果：

问题解决