减值函数MySQL

Question

我正在学习基本的MYSQL，我只想在这个查询中获得较少的值。

Select DATE_FORMAT(CURDATE(),'%Y') - DATE_FORMAT(HIRE_DATE,'%Y')      from employees;

有什么功能可以做到吗？

编辑：

我自己找到了解决办法。如果有人想看到是这样的：

select MIN(timestampdiff(year,HIRE_DATE,CURDATE())) as LessYearsWorking FROM employees;

Answer 1

至少你有一个least函数

mysql> select least(year(curdate()),year('2014-01-01')) as l;
+------+
| l    |
+------+
| 2014 |
+------+
1 row in set (0.04 sec)

要获得两个日期之间的年差，可以使用timestampdiff

mysql> select timestampdiff(year,'2014-01-01',curdate()) as d ;
+------+
| d    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

将两者结合起来，并获得相同查询中的差异和最小值，可以执行以下操作

select 
timestampdiff(year,'2014-01-01',curdate()) as d,
least(year(curdate()),year('2014-01-01')) as l ;

所以你的查询变成

select 
timestampdiff(year,HIRE_DATE,curdate()) as diff,
least(year(curdate()),year(HIRE_DATE)) as least_date
from employees