MySQL - 在 WHERE 中选择 AS

2021-12-19 00:00:00 sql select mysql

出于某种原因，这不起作用:

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 发生在 SELECT 子句上，它位于 WHERE 子句之前.

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'

相关文章