我可以在查询中使用稍后选择的列吗？

Question

想象一下这个查询…

SELECT `id`,
       `hits` + `other_hits` AS `total_hits`
  FROM `something`
 WHERE `hits` + `other_hits` > 30

如您所见，我已经重复了hits和的添加other_hits。我可以引用total_hits我在查询的其他部分中创建的列吗？

我尝试了一下，然后得到 1054：where子句中的Unknown列 。

Answer 1

使用：

SELECT `id`,
       `hits` + `other_hits` AS `total_hits`
  FROM `something`
HAVING `total_hits` > 30

最早的MySQL允许引用列别名是GROUP BY子句。后面的子句支持引用（HAVING，ORDER BY）。大多数其他数据库不支持在之前引用表别名ORDER BY，这通常需要使用派生表/内联视图：

SELECT t.id, t.total_hits
  FROM (SELECT `id`,
               `hits` + `other_hits` AS `total_hits`
          FROM `something`) t
 WHERE t.total_hits > 30

否则，您必须重用WHERE子句中的逻辑：

SELECT `id`,
       `hits` + `other_hits` AS `total_hits`
  FROM `something`
 WHERE `hits` + `other_hits` > 30