MySQL-在何处选择AS

问题描述：

由于某种原因，这是行不通的:

For some reason, this doesn't work:

select substring(rating, instr(rating,',') +1, +2) as val
from users where val = '15';

出现此错误:

错误1054(42S22):"where子句"中的未知列"val"

ERROR 1054 (42S22): Unknown column 'val' in 'where clause'

那我该怎么办?

答

首先，不能在WHERE子句上使用ALIAS.您应该使用该列，

First, you cannot use ALIAS on the WHERE clause. You shoul be using the column,

SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val 
FROM   users 
WHERE  SUBSTRING(rating, INSTR(rating,',') +1, +2) = '15'

原因如下:操作顺序为SQL，

The reason is as follows: the order of operation is SQL,

ALIAS发生在WHERE子句之前的SELECT子句上.

the ALIAS takes place on the SELECT clause which is before the WHERE clause.

如果您确实要使用别名，请将其包装在子查询中，

if you really want to use the alias, wrap it in a subquery,

SELECT *
FROM
    (
        SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val 
        FROM   users
    ) s
WHERE   val  = '15'