MYSQL:在 SELECT 中对自己定义的参数执行 where

Question

当我执行带有 AS 定义的 SELECT 语句并且我想对其进行过滤时，它会给出以下错误:#1054 - Unknown column 'TimeInSeconds' in 'where clause'

代码:

Select Id, time/10000000 as TimeInSeconds FROM Results WHERE
TimeInSeconds > 5 AND
TimeInSeconds < 36000

我理解错误是 TimeInSeconds 不是原始表的一部分，但是有没有办法使用这些定义的列？

Answer 1

也可以使用HAVING子句过滤掉自定义别名的结果

Select Id, `time`/10000000 as TimeInSeconds 
FROM Results 
HAVING TimeInSeconds > 5 AND TimeInSeconds < 36000

或者使用 WHERE 子句重复表达式

Select Id, `time`/10000000 as TimeInSeconds 
FROM Results 
WHERE (`time`/10000000) > 5 AND (`time`/10000000) < 36000