为什么我不能在Where子句中使用定义变量

My query is:

我查询的方法是:

SELECT 
  offer, 
  (SELECT 
        AVG(offer) 
   FROM 
        project_bids
  ) as var1 
FROM 
  `project_bids` 
WHERE 
  offer > var1

It causes "#1054 - Unknown column 'var1' in 'where clause'" error. Can anybody expalain why gives that error ? (i know working sql but i want to learn why it fails)

它导致“#1054 - where子句”中的未知列“var1”出错。有人能解释一下为什么会出现这种错误吗?(我知道如何使用sql，但我想知道为什么会失败)

3 个解决方案

#1

The sequence of execution of clauses of a SELECT statement is mentioned here:

SELECT语句子句的执行顺序如下:

http://blog.sqlauthority.com/2007/06/14/sql-server-easy-sequence-of-select-from-join-where-group-by-having-order-by/

Alias of an column can not be used in any clause except the last clause "ORDER BY".

除了最后一个子句“ORDER BY”之外，任何子句都不能使用列的别名。

#2

you would have to move "var1" out of the where and put in it to a having statement

您必须将“var1”移出where，并将其放入一个have语句中

the where statement does not have access to columns created in the select statement

where语句没有访问select语句中创建的列

#3

Write it as below:

把它写成如下:

SELECT offer, (SELECT AVG(offer) FROM project_bids) as var1 FROM project_bids WHERE offer > (SELECT AVG(offer) FROM project_bids)

从project_bid中选择offer(从project_bid中选择AVG(offer))作为var1，其中offer >(从project_bid中选择AVG(offer)))

#1