admin

MySQL - 搜索自定义列

sql

我想像这样运行 MySQL 查询-

 SELECT country_ID*2/id*3.159 as my_id
 FROM `state`
 WHERE my_id>2;
 ```

当我运行它时,我收到这样的错误-

1054 -“where 子句”中的未知列“my_id”

是否有任何替代解决方案可以在我新创建的虚拟列my_id 中进行搜索?

实际上,我正在尝试在Laravel Query Builder 中像这样进行搜索-

DB::table( ‘project’)->select( ‘project.id as id’,
‘project.completion_date as completion_date’,
DB::raw(‘FORMAT(project.total_cost_to_dispose - project.actual_cost_dispose, 2) as disposal_savings’)
)
->where(disposal_savings>100);
```

我可以做吗?

如果不是,那么 Laravel 或 MySQL 中的解决方案是什么?


阅读 209

收藏
2021-06-07

共1个答案

admin

您不能引用WHERE别名,请改用:

SELECT country_ID*2/id*3.159 as my_id 
FROM `state`
WHERE (country_ID*2/id*3.159)>2;

或使用子查询:

SELECT t.*
FROM
(
   SELECT country_ID*2/id*3.159 as my_id 
   FROM `state`
) as t
WHERE t.my_id>2
2021-06-07