MySQL-在何处选择AS [英] MySQL - SELECT AS in WHERE

问题描述

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

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,

• FROM子句
• WHERE子句
• GROUP BY子句
• HAVING子句
• SELECT子句
• ORDER BY子句

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'

这篇关于MySQL-在何处选择AS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！