意外结果-基于日期比较的MySQL选择

Question

我在MySQL select查询中遇到了一些意外的行为。我正在运行查询：

SELECT `refno`, `subdomain`, `toplevels`, `renew_until`, `expiry_date`, 
(YEAR(`renew_until`) - YEAR(`expiry_date`)) AS `renew_for` FROM `testing_names` 
WHERE `expiry_date` >= DATE(NOW()) AND `renew_for` >= 0

它返回(如预期的)：

|  refno  |  subdomain  |  toplevels  |  renew_until  |  expiry_date  |  renew_for  |
|-----------------------------------------------------------------------------------|
|  5      |  domain1    |  com        |  2014-02-02   |  2014-02-02   |  0          |
|  45     |  domain2    |  net        |  2014-01-27   |  2013-01-27   |  1          |

但是，下面的查询(注意renew_for上的不同比较)返回一个空集：

SELECT `refno`, `subdomain`, `toplevels`, `renew_until`, `expiry_date`, 
(YEAR(`renew_until`) - YEAR(`expiry_date`)) AS `renew_for` FROM `testing_names` 
WHERE `expiry_date` >= DATE(NOW()) AND `renew_for` > 0

在这个场景中，我期望的是第45行；我的查询出了什么问题？我是否以正确的方式使用renew_for？

Answer 1

其他RDBMS不允许这种语法:列别名不应该在WHERE子句中可用。

结果不稳定的MySQL does it's own thing though：

标准SQL不允许在WHERE子句中引用列别名。之所以施加此限制，是因为在执行WHERE代码时，可能尚未确定列值。

因此，将WHERE条件更改为以下内容

WHERE `expiry_date` >= DATE(NOW()) AND (YEAR(`renew_until`) - YEAR(`expiry_date`)) > 0

或者使用派生表

SELECT *
FROM
   (
    SELECT 
        `refno`, `subdomain`, `toplevels`, `renew_until`, `expiry_date`, 
         (YEAR(`renew_until`) - YEAR(`expiry_date`)) AS `renew_for`
    FROM `testing_names` 
    ) T
WHERE `expiry_date` >= DATE(NOW()) AND `renew_for` > 0

Answer 2

从更深的角度看，使用HAVING似乎可以用最简单的语法在MySQL中完成这项工作；但据我所知，这是MySQL做自己的事情( WHERE vs HAVING )的另一种情况；因此，如果优先考虑保持与SQL标准的一致性，@gbn的解决方案可能是最好的。

使用HAVING

SELECT `refno`, `subdomain`, `toplevels`, `renew_until`, `expiry_date`,
(YEAR(`renew_until`) - YEAR(`expiry_date`)) AS `renew_for` FROM `testing_names`
WHERE `expiry_date` >= DATE(NOW()) HAVING `renew_for` > 0