我可以在查询中使用稍后选择的列吗? [英] Can I use a column I have selected later in a query?

问题描述

想象一下这个查询...

Imagine this query...

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

如您所见，我已经重复添加了hits和other_hits.我可以参考在查询的其他部分创建的total_hits列吗?

As you can see, I've repeated the addition of hits and other_hits. Can I refer to total_hits column I created in other parts of the query?

我尝试了一下，然后得到 1054:where子句中的未知列.

I tried it, and I got 1054: Unknown column in where clause.

推荐答案

使用:

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

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

The earliest MySQL allows references to column aliases is the GROUP BY clause; clauses after that support references (HAVING, ORDER BY). Most other databases don't support referencing a table alias before the ORDER BY, which typically requires using a derived table/inline 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子句中的逻辑:

Otherwise, you have to reuse the logic in the WHERE clause:

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

这篇关于我可以在查询中使用稍后选择的列吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！