mysql - 如果不存在，我想将 `amount` 设置为 `-1`，下一步与 `-1` 进行比较

Question

我有这些记录:

Order
----------
id   amount
1    10
2    NULL
3    20

如果不存在，我想将 amount 设置为 -1，下一步与 -1 进行比较。

我这样写:

SELECT 
    id,
    COALESCE(o.amount, -1) AS amount2
FROM Order o
WHERE amount2 = -1

但是返回这个错误:

#1054 - Unknown column 'amount2' in 'where clause'

我想返回:

Order
----------
id   amount2
2    -1

Answer 1

您不能在 WHERE 子句中使用列别名。

试试这个:

SELECT 
  id,
  COALESCE(o.amount, -1) AS amount2
FROM Order o
WHERE COALESCE(o.amount, -1) = -1

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column

引自此处:http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html