mysql - 如何将 MySQL 变量放入 Where 子句

Question

我在这里有疑问:

select *,@final := DATE_ADD(start_date, INTERVAL 2 YEAR) AS final_date from emp_employee as emp
where employee_type_id=2
and (end_date is null or end_date>now());

结果是:

但是当我将查询更改为:

select *,@final := DATE_ADD(start_date, INTERVAL 2 YEAR) AS final_date from emp_employee as emp
where employee_type_id=2
and YEAR(@final)=2016
and (end_date is null or end_date>now());

它没有给出任何结果，它是空的。我的查询有什么问题以及如何解决？

Answer 1

您可以通过以下方式更改您的查询

select *, DATE_ADD(emp.start_date, INTERVAL 2 YEAR) AS final_date from emp_employee as emp
where emp.employee_type_id=2
and YEAR(DATE_ADD(emp.start_date, INTERVAL 2 YEAR))=2016
and (ISNULL(emp.end_date) or emp.end_date>now());

您必须使用 ISNULL 函数来检查特定列是否具有 NULL 值。