SELECT字段作为mytest WHERE mytest IS NULL-字段"mytest"未找到? [英] SELECT field as mytest WHERE mytest IS NULL - field "mytest" not found?

问题描述

我试图在where子句中使用选定字段的命名别名，但是数据库抱怨找不到该字段.

I am trying to use a selected field's named alias in a where clause, yet the database complains about being unable to find said field.

查询:

SELECT somefunction(myfield) as mytest, myotherfield as mytest2
FROM database.table
WHERE mytest IS NULL OR mytest2 IS NULL

期望:数据库将简单地测试 somefunction(myfield)或 mytest 的结果为空

Expectation: The db would simply test the result of somefunction(myfield) or mytest for being NULL

结果:数据库找不到 mytest 或 mytest2

有什么我想念的东西吗?为什么这样的东西行不通?

Is there something i am missing; why would something like this not work?

推荐答案

选择somefunction(myfield)作为mytest，将myotherfield作为mytest2 FROM表:mytest为NULL或mytest2为NULL

SELECT somefunction(myfield) as mytest, myotherfield as mytest2 FROM table WHERE mytest IS NULL OR mytest2 IS NULL

对不起，表格.

可能重复:

在WHERE中使用列别名MySQL查询的子句会产生错误

答案:

您只能在GROUP BY，ORDER BY或HAVING子句中使用列别名.

You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.

标准SQL不允许您在WHERE中引用列别名条款.施加此限制是因为当WHERE代码为执行，则可能尚未确定列值.

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

从 http://dev.mysql复制.com/doc/refman/5.0/en/problems-with-alias.html

这篇关于SELECT字段作为mytest WHERE mytest IS NULL-字段"mytest"未找到?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！