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

Question

想象一下这个查询...

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

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

我试过了，我得到了 1054: Unknown column in where clause。

Answer 1

使用:

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

MySQL最早允许引用列别名的是GROUP BY子句；之后的子句支持引用(HAVING、ORDER BY)。大多数其他数据库不支持在 ORDER BY 之前引用表别名，这通常需要使用派生表/内联 View :

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